The BETWEEN operator selects values that fall within a specified range, which can include numbers, text, or dates.
The BETWEEN operator inclusively includes both the beginning and end values specified in the range.
SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2; |
Here is an excerpt from the “Products” table in the Northwind sample database:
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 |
The following SQL statement retrieves all products priced between 10 and 20:
SELECT * FROM Products WHERE Price BETWEEN 10 AND 20; |
To display products that fall outside the range specified in the previous example, use NOT BETWEEN:
SELECT * FROM Products WHERE Price NOT BETWEEN 10 AND 20; |
The following SQL statement selects all products priced between 10 and 20 but excludes products with a CategoryID of 1, 2, or 3:
SELECT * FROM Products WHERE Price BETWEEN 10 AND 20 AND CategoryID NOT IN (1,2,3); |
The following SQL statement selects all products with a ProductName between “Carnarvon Tigers” and “Mozzarella di Giovanni”:
SELECT * FROM Products WHERE ProductName BETWEEN ‘Carnarvon Tigers’ AND ‘Mozzarella di Giovanni’ ORDER BY ProductName; |
The following SQL statement selects all products with a ProductName that falls between “Carnarvon Tigers” and “Chef Anton’s Cajun Seasoning”:
SELECT * FROM Products WHERE ProductName BETWEEN “Carnarvon Tigers” AND “Chef Anton’s Cajun Seasoning” ORDER BY ProductName; |
The following SQL statement selects all products with a ProductName that is not between “Carnarvon Tigers” and “Mozzarella di Giovanni”:
SELECT * FROM Products WHERE ProductName NOT BETWEEN ‘Carnarvon Tigers’ AND ‘Mozzarella di Giovanni’ ORDER BY ProductName; |
Here is a portion of the “Orders” table from the Northwind sample database:
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 |
The following SQL statement selects all orders with an OrderDate between ’01-July-1996′ and ’31-July-1996′:
SELECT * FROM Orders WHERE OrderDate BETWEEN ‘1996-07-01’ AND ‘1996-07-31’; |