MySQL | BETWEEN
Start your free 7-days trial now!
MySQL's BETWEEN operator returns values within a given range. It is inclusive of the start and end values, and can be used with numbers, strings and dates.
Syntax
SELECT column_name(s)FROM table_nameWHERE column_name BETWEEN value1 AND value2;
Examples
Consider the following table about some students:
student_id | fname | lname | day_enrolled | age | username |
|---|---|---|---|---|---|
1 | Sky | Towner | 2015-12-03 | 17 | stowner1 |
2 | Ben | Davis | 2016-04-20 | 19 | bdavis2 |
3 | Travis | Apple | 2018-08-14 | 18 | tapple3 |
4 | Arthur | David | 2016-04-01 | 16 | adavid4 |
5 | Benjamin | Town | 2014-01-01 | 17 | btown5 |
The above sample table can be created using the code here.
Numbers
To select people who are aged between 16 and 18:
SELECT * FROM studentsWHERE ageBETWEEN 16 AND 18;
+------------+----------+--------+--------------+------+----------+| student_id | fname | lname | day_enrolled | age | username |+------------+----------+--------+--------------+------+----------+| 1 | Sky | Towner | 2015-12-03 | 17 | stowner1 || 3 | Travis | Apple | 2018-08-14 | 18 | tapple3 || 4 | Arthur | David | 2016-04-01 | 16 | adavid4 || 5 | Benjamin | Town | 2014-01-01 | 17 | btown5 |+------------+----------+--------+--------------+------+----------+
Note that 'Arthur' and 'Travis', who are 16 and 18 respectively, are included in the results as the BETWEEN operator is inclusive of start and end values.
Strings
To find students with first name that is 'A' or greater (longer) and 'E' or shorter:
SELECT * FROM studentsWHERE fnameBETWEEN 'A' AND 'E';
+------------+----------+-------+--------------+------+----------+| student_id | fname | lname | day_enrolled | age | username |+------------+----------+-------+--------------+------+----------+| 2 | Ben | Davis | 2016-04-20 | 19 | bdavis2 || 4 | Arthur | David | 2016-04-01 | 16 | adavid4 || 5 | Benjamin | Town | 2014-01-01 | 17 | btown5 |+------------+----------+-------+--------------+------+----------+
Note that if someone had 'Ethan' as fname, then it would not be included in the results.
Dates
To find students who enrolled between 1 January 2014 and 1 April 2016 inclusive:
SELECT * FROM studentsWHERE day_enrolledBETWEEN '2014-01-01' AND '2016-04-01';
+------------+----------+--------+--------------+------+----------+| student_id | fname | lname | day_enrolled | age | username |+------------+----------+--------+--------------+------+----------+| 1 | Sky | Towner | 2015-12-03 | 17 | stowner1 || 4 | Arthur | David | 2016-04-01 | 16 | adavid4 || 5 | Benjamin | Town | 2014-01-01 | 17 | btown5 |+------------+----------+--------+--------------+------+----------+
