The BETWEEN operator selects values within a specified range, inclusive of both the beginning and end values.
This range can include numbers, text, or dates.
Retrieve all products with prices falling between 10 and 20.
SELECT * FROM Products WHERE Price BETWEEN 10 AND 20; |
|
Here is a snippet from the Products table utilized in the examples:
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 |
2 |
2 |
48 – 6 oz jars |
22 |
5 |
Chef Anton’s Gumbo Mix |
2 |
2 |
36 boxes |
21.35 |
To showcase products outside the range of the previous example, employ the NOT BETWEEN operator.
SELECT * FROM Products WHERE Price NOT BETWEEN 10 AND 20; |
The following SQL statement retrieves all products with prices ranging from 10 to 20. Furthermore, the CategoryID must be either 1, 2, or 3.
SELECT * FROM Products WHERE Price BETWEEN 10 AND 20 AND CategoryID IN (1,2,3); |
The following SQL statement retrieves all products with ProductName alphabetically 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 retrieves all products with ProductName 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 retrieves all products with ProductName not between “Carnarvon Tigers” and “Mozzarella di Giovanni”.
SELECT * FROM Products WHERE ProductName NOT BETWEEN ‘Carnarvon Tigers’ AND ‘Mozzarella di Giovanni’ ORDER BY ProductName; |
The following SQL statement retrieves all orders with an OrderDate between ’01-July-1996′ and ’31-July-1996′.
SELECT * FROM Orders WHERE OrderDate BETWEEN #07/01/1996# AND #07/31/1996#; |
OR:
SELECT * FROM Orders WHERE OrderDate BETWEEN ‘1996-07-01’ AND ‘1996-07-31’; |
Here is a snippet from the Orders table utilized in the examples:
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 |