» Basic MySQL Tutorial » Retrieving Data in a Range with SQL BETWEEN

Retrieving Data in a Range Using SQL BETWEEN

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:

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

+-------------+-------------------------------------+----------+
| 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

In this tutorial, you've learned how to use SQL BETWEEN to select data from database tables in a range.

Search