Example
You can use BETWEEN clause to replace a combination of "greater than equal AND less than equal" conditions.
Data
+----+-----------+
| id | username |
+----+-----------+
| 1 | admin |
| 2 | root |
| 3 | toor |
| 4 | mysql |
| 5 | thanks |
| 6 | java |
+----+-----------+
Query with operators
SELECT * FROM stack WHERE id >= 2 and id <= 5;
Similar query with BETWEEN
SELECT * FROM stack WHERE id BETWEEN 2 and 5;
Result
+----+-----------+
| id | username |
+----+-----------+
| 2 | root |
| 3 | toor |
| 4 | mysql |
| 5 | thanks |
+----+-----------+
4 rows in set (0.00 sec)
Note
BETWEEN uses
>=and<=, not>and<.
Using NOT BETWEEN
If you want to use the negative you can use NOT. For example :
SELECT * FROM stack WHERE id NOT BETWEEN 2 and 5;
Result
+----+-----------+
| id | username |
+----+-----------+
| 1 | admin |
| 6 | java |
+----+-----------+
2 rows in set (0.00 sec)
Note
NOT BETWEEN uses
>and<and not>=and<=That is,WHERE id NOT BETWEEN 2 and 5is the same asWHERE (id < 2 OR id > 5).
If you have an index on a column you use in a BETWEEN search, MySQL can use that index for a range scan.
