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:

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"):

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'