Learn how to use the SQL BETWEEN operator with Product and Order tables.
The BETWEEN operator in SQL selects values within a given range. These values can be numbers, text, or dates. The operator is inclusive, meaning both the beginning and end values are included in the result.
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
Let’s create a table called Products to store product data. Here’s how to define it:
CREATE TABLE Products (
ProductID INT,
ProductName VARCHAR(100),
SupplierID INT,
CategoryID INT,
Unit VARCHAR(50),
Price DECIMAL(10, 2)
);
-- Insert sample data into the table
INSERT INTO Products (ProductID, ProductName, SupplierID, CategoryID, Unit, Price) VALUES (1, 'Chais', 1, 1, '10 boxes x 20 bags', 18.00);
INSERT INTO Products (ProductID, ProductName, SupplierID, CategoryID, Unit, Price) VALUES (2, 'Chang', 1, 1, '24 - 12 oz bottles', 19.00);
INSERT INTO Products (ProductID, ProductName, SupplierID, CategoryID, Unit, Price) VALUES (3, 'Aniseed Syrup', 1, 2, '12 - 550 ml bottles', 10.00);
INSERT INTO Products (ProductID, ProductName, SupplierID, CategoryID, Unit, Price) VALUES (4, "Chef Anton's Cajun Seasoning", 2, 2, '48 - 6 oz jars', 22.00);
INSERT INTO Products (ProductID, ProductName, SupplierID, CategoryID, Unit, Price) VALUES (5, "Chef Anton's Gumbo Mix", 2, 2, '36 boxes', 21.35);
We can use the BETWEEN operator to filter records by a range of values. Let’s say we want to get all products with a price between 10 and 20. The SQL query would look like this:
SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;
Explanation: This query retrieves all products where the price is between 10 and 20 (inclusive).
Example Output:
| ProductID | ProductName | Price |
|---|---|---|
| 1 | Chais | 18.00 |
| 2 | Chang | 19.00 |
| 3 | Aniseed Syrup | 10.00 |
Now let’s find all products that do not have a price between 10 and 20. The SQL query would be:
SELECT * FROM Products
WHERE Price NOT BETWEEN 10 AND 20;
Explanation: This query retrieves all products where the price is not between 10 and 20.
Example Output:
| ProductID | ProductName | Price |
|---|---|---|
| 4 | Chef Anton’s Cajun Seasoning | 22.00 |
| 5 | Chef Anton’s Gumbo Mix | 21.35 |
We can also use the BETWEEN operator with text values. Let’s say we want to find products with a ProductName alphabetically between ‘Carnarvon Tigers’ and ‘Mozzarella di Giovanni’. The SQL query would be:
SELECT * FROM Products
WHERE ProductName BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni'
ORDER BY ProductName;
This query will select products whose names are alphabetically between the specified product names.
We can also use the BETWEEN operator with dates. Let’s say we want to select orders made between ’01-July-1996′ and ’31-July-1996′. The SQL query would be:
SELECT * FROM Orders
WHERE OrderDate BETWEEN '1996-07-01' AND '1996-07-31';
Explanation: This query retrieves all orders placed between the given date range.