The BETWEEN operator is used to select values within a range.
The SQL BETWEEN Operator
The BETWEEN operator selects values within a range. The values can be numbers, text, or dates.
SQL BETWEEN Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
Demo Database
In this tutorial we will use the well-known Northwind sample database.
Below is a selection from the "Products" table:
| ProductID | ProductName | SupplierID | CategoryID | Unit | Price |
|---|---|---|---|---|---|
| 1 | Chais | 1 | 1 | 10 boxes x 20 bags | 18 |
| 2 | Chang | 1 | 1 | 24 - 12 oz bottles | 19 |
| 3 | Aniseed Syrup | 1 | 2 | 12 - 550 ml bottles | 10 |
| 4 | Chef Anton's Cajun Seasoning | 1 | 2 | 48 - 6 oz jars | 22 |
| 5 | Chef Anton's Gumbo Mix | 1 | 2 | 36 boxes | 21.35 |
BETWEEN Operator Example
The following SQL statement selects all products with a price BETWEEN 10 and 20:
NOT BETWEEN Operator Example
To display the products outside the range of the previous example, use NOT BETWEEN:
BETWEEN Operator with IN Example
The following SQL statement selects all products with a price BETWEEN 10 and 20, but products with a CategoryID of 1,2, or 3 should not be displayed:
Example
SELECT * FROM Products
WHERE (Price BETWEEN 10 AND 20)
AND NOT
CategoryID IN (1,2,3);
Try it Yourself »
BETWEEN Operator with Text Value Example
The following SQL statement selects all products with a ProductName beginning with any of the letter BETWEEN 'C' and 'M':
NOT BETWEEN Operator with Text Value Example
The following SQL statement selects all products with a ProductName beginning with any of the letter NOT BETWEEN 'C' and 'M':
Sample Table
Below is a selection from the "Orders" table:
| OrderID | CustomerID | EmployeeID | OrderDate | ShipperID |
|---|---|---|---|---|
| 10248 | 90 | 5 | 7/4/1996 | 3 |
| 10249 | 81 | 6 | 7/5/1996 | 1 |
| 10250 | 34 | 4 | 7/8/1996 | 2 |
| 10251 | 84 | 3 | 7/9/1996 | 1 |
| 10252 | 76 | 4 | 7/10/1996 | 2 |
BETWEEN Operator with Date Value Example
The following SQL statement selects all orders with an OrderDate BETWEEN '04-July-1996' and '09-July-1996':
Example
SELECT * FROM Orders
WHERE OrderDate BETWEEN #07/04/1996# AND
#07/09/1996#;
Try it Yourself »
Notice that the BETWEEN operator can produce different result in different databases!
In some databases, BETWEEN selects fields that are between and excluding the test values.
In other databases, BETWEEN selects fields that are between and including the test values.
And in other databases, BETWEEN selects fields between the test values, including the first test value and excluding the
last test value.
Therefore: Check how your database treats the BETWEEN operator!