MySQL | NOT BETWEEN
Start your free 7-days trial now!
MySQL's NOT BETWEEN operator returns values that are not within a given range. The start and end values are considered part of the range, hence are excluded from the result of NOT BETWEEN.
Syntax
SELECT column_name(s)FROM table_nameWHERE column_name NOT 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 students who are not between age 16 and 18:
SELECT * FROM studentsWHERE ageNOT BETWEEN 16 AND 18;
+------------+-------+-------+--------------+------+----------+| student_id | fname | lname | day_enrolled | age | username |+------------+-------+-------+--------------+------+----------+| 2 | Ben | Davis | 2016-04-20 | 19 | bdavis2 |+------------+-------+-------+--------------+------+----------+
Note that 'Arthur' and 'Travis', who are 16 and 18 respectively, are not included in the results as the NOT BETWEEN operator is exclusive of start and end values.
Strings
To find students with first name that is less than 'A' or greater than 'E' :
SELECT * FROM studentsWHERE fnameNOT BETWEEN 'A' AND 'E';
+------------+--------+--------+--------------+------+----------+| student_id | fname | lname | day_enrolled | age | username |+------------+--------+--------+--------------+------+----------+| 1 | Sky | Towner | 2015-12-03 | 17 | stowner1 || 3 | Travis | Apple | 2018-08-14 | 18 | tapple3 |+------------+--------+--------+--------------+------+----------+
Note that 'Arthur', 'Ben' and 'Benjamin' who all have names that have string value between 'A' and 'E' are not returned.
Dates
To find students who did not enroll between 1 January 2014 and 1 April 2016:
SELECT * FROM studentsWHERE day_enrolledNOT BETWEEN '2014-01-01' AND '2016-04-01';
+------------+--------+-------+--------------+------+----------+| student_id | fname | lname | day_enrolled | age | username |+------------+--------+-------+--------------+------+----------+| 2 | Ben | Davis | 2016-04-20 | 19 | bdavis2 || 3 | Travis | Apple | 2018-08-14 | 18 | tapple3 |+------------+--------+-------+--------------+------+----------+
