SQL BETWEEN Statement

SQL BETWEEN Description

The BETWEEN operator selects values in a given range. Values can be numbers, text, or dates.

The BETWEEN operator selects data including start and end values.

BETWEEN Syntax

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

Demonstration database

Below is a sample from the "Products" table:

ProductID	ProductName	SupplierID	CategoryID	Unit	Price
1	Chais	1	1	10 boxes x 20 bags	18.00
2	Chang	1	1	24 - 12 oz bottles	19.00
3	Aniseed Syrup	1	2	12 - 550 ml bottles	10.00
4	Chef Anton's Cajun Seasoning	2	2	48 - 6 oz jars	22.00
5	Chef Anton's Gumbo Mix	2	2	36 boxes	21.35

SQL BETWEEN Example

The following SQL statement selects all products with a price between 10 and 20:

Run SQLSELECT * FROM Products 
WHERE Price BETWEEN 10 AND 20

NOT BETWEEN Example

To display products outside the range of the previous example, use NOT BETWEEN:

Run SQLSELECT * FROM Products 
WHERE Price NOT BETWEEN 10 AND 20

BETWEEN with IN Example

The following SQL statement selects all products with a price between 10 and 20 and, at the same time, does not show products with CategoryID 1, 2, or 3:

Run SQLSELECT * FROM Products 
WHERE Price BETWEEN 10 AND 20 
AND CategoryID NOT IN (1,2,3)

BETWEEN with text values Example

The following SQL statement selects all products from ProductName between "Carnarvon Tigers" and "Mozzarella di Giovanni":

Run SQLSELECT * FROM Products 
WHERE ProductName BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni' 
ORDER BY ProductName

The following SQL statement selects all products from ProductName between "Carnarvon Tigers" and "Chef Anton's Cajun Seasoning":

Run SQLSELECT * FROM Products 
WHERE ProductName BETWEEN 'Carnarvon Tigers' AND 'Chartreuse verte' 
ORDER BY ProductName

NOT BETWEEN with text values Example

The following SQL statement selects all products from ProductName not between "Carnarvon Tigers" and "Mozzarella di Giovanni":

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

Table sample

Below is a sample from the table "Orders" ("Orders"):

ProductID	OrderID	CustomerID	EmployeeID	OrderDate	ShipperID
1	10248	90	5	1996-07-04	3
2	10249	81	6	1996-07-05	1
3	10250	34	4	1996-07-08	2
4	10251	84	3	1996-07-08	1
5	10252	76	4	1996-07-09	2

BETWEEN with dates Example

The following SQL statement selects all orders (Orders) from OrderDate between '01-July-1996' and '31-July-1996':

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

or:

Run SQLSELECT * FROM Orders 
WHERE OrderDate BETWEEN '1996-07-01' AND '1996-07-31'