SQL Between
The SQL BETWEEN Operator
Values inside a certain range are chosen by the BETWEEN operator. Dates, text, or numbers can be used as the values.
The values at the beginning and end are included in the BETWEEN operator.
Example
Selects all products with a price between 10 and 20:
SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;
Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
Demo 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 | 2 | 2 | 48 - 6 oz jars | 22 |
| 5 | Chef Anton's Gumbo Mix | 2 | 2 | 36 boxes | 21.35 |
NOT BETWEEN
Use NOT BETWEEN to display the products outside of the preceding example’s range:
Example
SELECT * FROM Products
WHERE Price NOT BETWEEN 10 AND 20;
BETWEEN with IN
The goods with prices between 10 and 20 are all chosen by the SQL query that follows. Furthermore, the CategoryID needs to be one of two or three:
Example
SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20
AND CategoryID IN (1,2,3);
BETWEEN Text Values
The items between Carnarvon Tigers and Mozzarella di Giovanni that have an alphabetical ProductName are selected using the SQL statement that follows:
Example
SELECT * FROM Products
WHERE ProductName BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni'
ORDER BY ProductName;
All goods with a ProductName between Carnarvon Tigers and Chef Anton’s Cajun Seasoning are selected by the SQL query that follows:
Example
SELECT * FROM Products
WHERE ProductName BETWEEN "Carnarvon Tigers" AND "Chef Anton's Cajun Seasoning"
ORDER BY ProductName;
NOT BETWEEN Text Values
All items with a ProductName that is not between Carnarvon Tigers and Mozzarella di Giovanni are chosen using the SQL statement that follows:
Example
SELECT * FROM Products
WHERE ProductName NOT BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni'
ORDER BY ProductName;
BETWEEN Dates
All orders with an OrderDate between “01-July-1996” and “31-July-1996” are selected using the SQL statement that follows:
Example
SELECT * FROM Orders
WHERE OrderDate BETWEEN #07/01/1996# AND #07/31/1996#;
OR:
Example
SELECT * FROM Orders
WHERE OrderDate BETWEEN '1996-07-01' AND '1996-07-31';
Sample 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 |
