SQL BETWEEN Operator

The BETWEEN operator in SQL is a convenient way to filter data based on a range of values. It selects all values that fall within a specified minimum and maximum, inclusive.

BETWEEN: Definition and Usage

BETWEEN works with numeric, text (alphabetical order), and date data types. Crucially, it's *inclusive*, meaning both the lower and upper bounds of the range are included in the results.

Syntax


SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

--To exclude values within the range:
SELECT column_name(s)
FROM table_name
WHERE column_name NOT BETWEEN value1 AND value2;

Example Database: Products Table

The following examples use this sample '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	2	2	48 - 6 oz jars	22
5	Chef Anton's Gumbo Mix	2	2	36 boxes	21.35

Examples

Numeric Values

Selecting products with prices between 10 and 20:

Syntax


SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;

Output


ProductID | ProductName        | SupplierID | CategoryID | Unit                     | Price
------------------------------------------------------------------------------------
3         | Aniseed Syrup       | 1          | 2          | 12 - 550 ml bottles      | 10
1         | Chais                | 1          | 1          | 10 boxes x 20 bags      | 18
2         | Chang                | 1          | 1          | 24 - 12 oz bottles      | 19
5         | Chef Anton's Gumbo Mix | 2          | 2          | 36 boxes                 | 21.35

Numeric Values (NOT BETWEEN)

Selecting products with prices *outside* the range of 10 to 20:

Syntax


SELECT * FROM Products
WHERE Price NOT BETWEEN 10 AND 20;

Output


ProductID | ProductName              | SupplierID | CategoryID | Unit                     | Price
------------------------------------------------------------------------------------
4         | Chef Anton's Cajun Seasoning | 2          | 2          | 48 - 6 oz jars          | 22

Numeric Values with Additional Condition

Combining BETWEEN with IN:

Syntax


SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20
AND CategoryID IN (1, 2, 3);

Output


ProductID | ProductName        | SupplierID | CategoryID | Unit                     | Price
------------------------------------------------------------------------------------
3         | Aniseed Syrup       | 1          | 2          | 12 - 550 ml bottles      | 10
1         | Chais                | 1          | 1          | 10 boxes x 20 bags      | 18
2         | Chang                | 1          | 1          | 24 - 12 oz bottles      | 19
5         | Chef Anton's Gumbo Mix | 2          | 2          | 36 boxes                 | 21.35

Text Values

Selecting products with names alphabetically between two names:

Syntax


SELECT * FROM Products
WHERE ProductName BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni'
ORDER BY ProductName;

Output


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

Text Values (NOT BETWEEN)

Selecting products with names *outside* the specified alphabetical range:

Syntax


SELECT * FROM Products
WHERE ProductName NOT BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni'
ORDER BY ProductName;

Output


ProductID | ProductName              | SupplierID | CategoryID | Unit                     | Price
------------------------------------------------------------------------------------
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

Example Database: Orders Table

The date examples use this sample '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

Date Values

Selecting orders placed between July 1st and July 31st, 1996:

Syntax


SELECT * FROM Orders
WHERE OrderDate BETWEEN #07/01/1996# AND #07/31/1996#;

--Alternative date format:
SELECT * FROM Orders
WHERE OrderDate BETWEEN '1996-07-01' AND '1996-07-31';

Output


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