How to use the special SQL syntax to select rows with values in a given range, or outside of it. How to deal with bounds in a reversed order.
Finding values in a given range
Example: Which employees have a salary in this range?
SELECT id FROM employee WHERE salary BETWEEN 60000 AND 75000;The BETWEEN syntax is specific to SQL. It is suppoerted by all DBMSs. It is a less verbose, more expressive equivalent for:
SELECT id FROM employee WHERE salary >= 60000 AND salary <= 75000;Finding values out of a given range
Example: Which employees have a lower or higher salary than this range?
SELECT is FROM employee WHERE salary NOT BETWEEN 60000 AND 75000;Dealing with NULLs
NULL pseudovalues escape both the BETWEEN and NOT BETWEEN operators. To include NULLs in a NOT BETWEEN condition:
SELECT id FROM employee WHERE salary IS NULL OR salary BETWEEN 60000 AND 75000;Other data types
The same syntax works with any data type:
SELECT is FROM employee WHERE name BETWEEN 'C' AND 'D';
SELECT is FROM employee WHERE birth_date BETWEEN '1990-01-01' AND '1994-01-01';Unordered bounds
Normally you should specify the bounds in the correct order, for example: BETWEEN 1 AND 2.
When an application composes a query of this type with input entered by the user, it should do additional checks to make sure that the order is correct. But this can be avoided by using this syntax (not available in some DBMSs):
SELECT id FROM employee WHERE salary BETWEEN SYMMETRIC 60000 AND 75000;
SELECT id FROM employee WHERE salary NOT BETWEEN SYMMETRIC 60000 AND 75000;