Retrieving Data in a Range with SQL BETWEEN

SQL BETWEEN allows you to retrieve values within a specific range. The usage of SQL 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_rage and upper_range as well as the values lower_rage and upper_range. The query which is equivalent to SQL BETWEEN to get the same result is

SELECT column_list

FROM table_name

WHERE column_1 >= lower_range AND column_1 <= upper_range

Let’s practice with several examples of using SQL BETWEEN to search values in a range.

Suppose we want to find all products which buy price is in a range of 90$ and 100$, we can perform the following query to do so:

SELECT productCode,ProductName,buyPrice

FROM products

WHERE buyPrice BETWEEN 90 AND 100

ORDER BY buyPrice DESC

Here is the output

+-------------+--------------------------------------+----------+
| productCode | ProductName                          | buyPrice |
+-------------+--------------------------------------+----------+
| S10_1949    | 1952 Alpine Renault 1300             |    98.58 |
| S24_3856    | 1956 Porsche 356A Coupe              |     98.3 |
| S12_1108    | 2001 Ferrari Enzo                    |    95.59 |
| S12_1099    | 1968 Ford Mustang                    |    95.34 |
| S18_1984    | 1995 Honda Civic                     |    93.89 |
| S18_4027    | 1970 Triumph Spitfire                |    91.92 |
| S10_4698    | 2003 Harley-Davidson Eagle Drag Bike |    91.02 |
+-------------+--------------------------------------+----------+

The output contains all products in the range of 90$ and 100$, and if there is a product with buy price 90$ or 100$, it will be included in the output too.

In order to find all records which are not in a range we use NOT BETWEEN. To find all products that buy price outside the range of 20 and 100, we can operate following query:

SELECT productCode,ProductName,buyPrice

FROM products

WHERE buyPrice NOT BETWEEN 20 AND 100

ORDER BY buyPrice DESC

+-------------+-------------------------------------+----------+
| productCode | ProductName                         | buyPrice |
+-------------+-------------------------------------+----------+
| S10_4962    | 1962 LanciaA Delta 16V              |   103.42 |
| S18_2238    | 1998 Chrysler Plymouth Prowler      |   101.51 |
| S24_2972    | 1982 Lamborghini Diablo             |    16.24 |
| S24_2840    | 1958 Chevy Corvette Limited Edition |    15.91 |
+-------------+-------------------------------------+----------+

The query above is equivalent to the following query

SELECT productCode,ProductName,buyPrice

FROM products

WHERE buyPrice < 20 OR buyPrice > 100

ORDER BY buyPrice DESC