SELECT with BETWEEN

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 5 is the same as WHERE (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.

Found a mistake? Have a question or improvement idea? Let me know.

Table Of Contents