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