sql_between

SQL BETWEEN Operator: Selecting Values Within a Range

The BETWEEN operator is used in a WHERE clause to select values that fall within a specified range. This range is inclusive of the start and end values.

The SQL BETWEEN Operator Explained

The BETWEEN operator allows you to filter data based on whether a column’s value is greater than or equal to a minimum value AND less than or equal to a maximum value. It can be used with numbers, text (string) values, and dates.

column_name BETWEEN value1 AND value2 is equivalent to column_name >= value1 AND column_name <= value2.

SQL BETWEEN Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN lower_bound AND upper_bound;

Demonstration Database: Northwind

We’ll use the 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.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

BETWEEN Operator Example (Numeric)

The following SQL statement selects all products with a price between $10 and $20 (inclusive):

SELECT ProductName, Price
FROM Products
WHERE Price BETWEEN 10.00 AND 20.00
ORDER BY Price;

NOT BETWEEN Operator Example

To display products outside a specific range, use NOT BETWEEN:

SELECT ProductName, Price
FROM Products
WHERE Price NOT BETWEEN 10.00 AND 20.00
ORDER BY Price;

BETWEEN Operator with IN Example (Combining Conditions)

The following SQL statement selects products with a price between $10 and $20, but excludes products from CategoryID 1, 2, or 3:

SELECT ProductName, Price, CategoryID
FROM Products
WHERE (Price BETWEEN 10.00 AND 20.00)
  AND CategoryID NOT IN (1, 2, 3)
ORDER BY Price;

BETWEEN Operator with Text Values Example

When used with text, BETWEEN works based on alphabetical order. The following SQL statement selects all products with a ProductName starting with a letter alphabetically between ‘C’ and ‘M’ (inclusive of ‘C’ and up to, but not including, names starting ‘N’).

SELECT ProductName
FROM Products
WHERE ProductName BETWEEN 'C' AND 'Mzzzzzz' -- To include all names starting with M
ORDER BY ProductName;

A more precise way for names starting from C up to M (inclusive for M):

SELECT ProductName
FROM Products
WHERE ProductName >= 'C' AND ProductName < 'N'
ORDER BY ProductName;

Or, if you strictly mean products that start with letters C through M:

SELECT ProductName
FROM Products
WHERE SUBSTRING(ProductName, 1, 1) BETWEEN 'C' AND 'M'
ORDER BY ProductName;

Sample Table: “Orders”

Below is a selection from the “Orders” table with OrderDate column:

OrderID
CustomerID
EmployeeID
OrderDate
ShipperID


10248
90
5
1996-07-04
3


10249
81
6
1996-07-05
1


10250
34
4
1996-07-08
2


10251
84
3
1996-07-09
1


10252
76
4
1996-07-10
2

BETWEEN Operator with Date Values Example

The following SQL statement selects all orders with an OrderDate between July 4, 1996, and July 9, 1996 (inclusive). Note the standard ISO 8601 date format (‘YYYY-MM-DD’).

SELECT OrderID, CustomerID, OrderDate
FROM Orders
WHERE OrderDate BETWEEN '1996-07-04' AND '1996-07-09'
ORDER BY OrderDate;

Database Behavior: Standard SQL specifies that BETWEEN is inclusive. Most modern, compliant database systems follow this standard. If you encounter unexpected behavior, especially with older systems, consult its documentation, but generally, X BETWEEN Y AND Z means X >= Y AND X <= Z.

Practical Application: BETWEEN is very useful for filtering by date ranges (e.g., sales this month), price ranges, or any numerical or textual data that has a logical order.