SQL BETWEEN Operator

The BETWEEN operator is used to select values within a range. It selects values within a range. The values can be numbers, text, or dates.

SQL BETWEEN Syntax

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

Demo Database

In this tutorial we will use the well-known Northwind sample database.

Below is a selection from the “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 1 2 48 – 6 oz jars 22
5 Chef Anton’s Gumbo Mix 1 2 36 boxes 21.35

BETWEEN Operator Example

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

Example

SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;

NOT BETWEEN Operator Example

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

Example

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

BETWEEN Operator with IN Example

The following SQL statement selects all products with a price BETWEEN 10 and 20, but products with a CategoryID of 1,2, or 3 should not be displayed:

Example

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

BETWEEN Operator with Text Value Example

The following SQL statement selects all products with a ProductName beginning with any of the letter BETWEEN ‘C’ and ‘M’:

Example

SELECT * FROM Products
WHERE ProductName BETWEEN 'C' AND 'M';

NOT BETWEEN Operator with Text Value Example

The following SQL statement selects all products with a ProductName beginning with any of the letter NOT BETWEEN ‘C’ and ‘M’:

Example

SELECT * FROM Products
WHERE ProductName NOT BETWEEN 'C' AND 'M';

Sample Table

Below is a selection from the “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

BETWEEN Operator with Date Value Example

The following SQL statement selects all orders with an OrderDate BETWEEN ’04-July-1996′ and ’09-July-1996′:

Example

SELECT * FROM Orders
WHERE OrderDate BETWEEN #07/04/1996# AND #07/09/1996#;

Sometime the BETWEEN operator may be produce different result in different databases.

Pankaj Singh Sugara

Author Pankaj Singh Sugara

Experienced Software Engineer with a demonstrated history of working in the marketing and advertising industry. Skilled in SQL, Web Applications, PHP, WordPress, and Joomla. Strong engineering professional with a B-TECH focused in Information Technology from JCDM College of Engineering Sirsa, Haryana.

More posts by Pankaj Singh Sugara