BETWEEN: Filtering by a Range

BETWEEN filters rows where a value falls within a range. Both boundary values are inclusive.

It works with numbers, dates, and strings.

Prior lesson

The sample dataset is defined in the Introduction to SQL lesson.

Syntax

SELECT columns
FROM table_name
WHERE column BETWEEN lower_bound AND upper_bound;

-- Equivalent to:
WHERE column >= lower_bound AND column <= upper_bound

Examples

Example 1: Numeric range

Return orders where the total amount is between $50 and $500 (inclusive).

SELECT
    order_id,
    total_amount,
    status
FROM orders
WHERE total_amount BETWEEN 50 AND 500;

Result:

order_id	total_amount	status
2	59.98	completed
8	89.97	completed
3	349.00	completed
6	349.00	cancelled

Example 2: Date range

Find orders placed in Q1 2024 (January through March).

SELECT
    order_id,
    order_date,
    total_amount
FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31';

Result:

order_id	order_date	total_amount
1	2024-01-05	999.00
2	2024-01-10	59.98
3	2024-01-15	349.00
4	2024-02-01	796.00
5	2024-02-10	49.90
6	2024-03-01	349.00
7	2024-03-15	999.00

Example 3: NOT BETWEEN

Exclude a range of values.

SELECT
    name,
    price
FROM products
WHERE price NOT BETWEEN 10 AND 300;

Result:

name	price
Laptop	999.00
Desk	349.00
Notebook	4.99

Common Mistake

Both bounds are inclusive

BETWEEN 50 AND 100 includes rows where the value is exactly 50 or exactly 100. This is different from some programming languages where ranges are half-open.

For dates, this matters: BETWEEN '2024-01-01' AND '2024-03-31' includes March 31st entirely (or up to 2024-03-31 00:00:00 in databases that store timestamps). If your date column stores timestamps, use < '2024-04-01' instead to capture the full last day.

-- Safer date range for timestamp columns:
WHERE order_date >= '2024-01-01' AND order_date < '2024-04-01'

Practice

Write a query that returns all products with a price between $20 and $400 (inclusive), showing name, category, and price.

Show answer

SELECT
    name,
    category,
    price
FROM products
WHERE price BETWEEN 20 AND 400;

Expected result:

name	category	price
Mouse	Electronics	29.99
Chair	Furniture	199.00
Desk	Furniture	349.00