Summary: In this tutorial, you will learn how to retrieve data from database tables which its value are in a range by using SQL BETWEEN operator.
SQL BETWEEN Operator Syntax
The SQL BETWEEN operator allows you to retrieve values within a specific range. the SQL between must be used in the WHERE clause of the SQL SELECT statement. The following illustrates the SQL BETWEEN syntax:
3 |
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
3 |
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:
1 |
SELECT productCode,ProductName,buyPrice |
3 |
WHERE buyPrice BETWEEN 90 AND 100 |
+-------------+--------------------------------------+----------+
| 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:
1 |
SELECT productCode,ProductName,buyPrice |
3 |
WHERE buyPrice NOT BETWEEN 20 AND 100 |
+-------------+-------------------------------------+----------+
| 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
1 |
SELECT productCode,ProductName,buyPrice |
3 |
WHERE buyPrice < 20 OR buyPrice > 100 |
In this tutorial, you’ve learned how to use SQL BETWEEN to select data from database tables in a range.
Related posts:
- Selecting Data with SQL IN
- Using MySQL SELECT Statement to Query Data
- How to Use MySQL Limit to Constrain Number of Returned Records
- Managing Databases in MySQL
- Working with Database Table – Part I
