BETWEEN…AND allows you to retrieve values within a specific range. The usage of BETWEEN is as follows:
SELECT column_list FROM table_name WHERE column_1 BETWEEN lower_range AND upper_range
MySQL returns all records in which the column_1 value is in the range of lower_range and upper_range including the values lower_range and upper_range.
Suppose we want to find the number of employees whose salaries are in between 150000 and 200000. The query is as follows.
SELECT COUNT(*) FROM salaries WHERE salary BETWEEN 150000 AND 200000;
Mysql also has an operator NOT BETWEEN…AND which will retrieve values not in the range specified.
SELECT column_list FROM table_name WHERE column_1 NOT BETWEEN lower_range AND upper_range
Example
SELECT COUNT(*) FROM salaries WHERE salary NOT BETWEEN 150000 AND 200000;
