TOP

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:

ProductIDProductNameSupplierIDCategoryIDUnitPrice
1Chais1110 boxes x 20 bags18.00
2Chang1124 - 12 oz bottles19.00
3Aniseed Syrup1212 - 550 ml bottles10.00
4Chef Anton's Cajun Seasoning2248 - 6 oz jars22.00
5Chef Anton's Gumbo Mix2236 boxes21.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"):

ProductIDOrderIDCustomerIDEmployeeIDOrderDateShipperID
1102489051996-07-043
2102498161996-07-051
3102503441996-07-082
4102518431996-07-081
5102527641996-07-092

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'