How to Use BETWEEN in SQL

Use BETWEEN when a value should fall within an inclusive start and end range. This is the cleanest way to filter dates, prices, scores, and IDs without writing two comparison operators.

What you’ll build or solve

You’ll learn how to use BETWEEN in SQL for numeric, date, and text ranges. You’ll also know how inclusive boundaries work.

When this approach works best

This approach is the right choice when a value must stay inside a clear lower and upper bound.

Common real-world scenarios include:

• Price filters
• Date ranges
• Score brackets
• Order ID spans
• Created-at reports

This is a bad idea when the values are a list of unrelated exact matches. In that case, use IN.

Prerequisites

You only need:

• A SQL table with numeric, date, or sortable text columns
• Basic SELECT and WHERE knowledge

Step-by-step instructions

Step 1: Place BETWEEN after the column name

Use the lower value first, then the upper value.

SELECT *
FROM products
WHERE price BETWEEN 50 AND 100;

This includes both 50 and 100.

It works especially well for dates.

SELECT *
FROM orders
WHERE created_at BETWEEN '2026-01-01' AND '2026-01-31';

This is equivalent to:

WHERE created_at >= '2026-01-01'
AND created_at <= '2026-01-31'

What to look for:

• BETWEEN is inclusive
• Lower value comes first
• Great for numbers and dates
• Cleaner than two comparisons
• Use exact date boundaries carefully with timestamps

Examples you can copy

Price range

SELECT *
FROM products
WHERE price BETWEEN 20 AND 80;

January orders

SELECT *
FROM orders
WHERE created_at BETWEEN '2026-01-01' AND '2026-01-31';

Score filter

SELECT *
FROM exams
WHERE score BETWEEN 80 AND 100;

Common mistakes and how to fix them

Mistake 1: Reversing the bounds

What the reader might do:

WHERE price BETWEEN 100 AND 50

Why it breaks: the lower bound must come first.

Corrected approach:

WHERE price BETWEEN 50 AND 100

Mistake 2: Forgetting timestamp precision in dates

What the reader might do:

WHERE created_at BETWEEN '2026-01-01' AND '2026-01-31'

Why it breaks: rows later on 2026-01-31 with time values may be excluded in some systems.

Corrected approach:

Use an exclusive upper bound when time exists.

WHERE created_at >= '2026-01-01'
AND created_at < '2026-02-01'

Mistake 3: Using BETWEEN for exact lists

What the reader might do:

WHERE status BETWEEN 'paid' AND 'refunded'

Why it breaks: this creates a lexical range, not a specific value list.

Corrected approach:

Use IN for exact string options.

Troubleshooting

If no rows match, confirm the lower value comes first.

If date edges look wrong, account for timestamp precision.

If the result should exclude the edges, use > and < instead.

If the filter is a list, switch to IN.

Quick recap

• Use BETWEEN for inclusive ranges
• Lower bound goes first
• Great for prices, dates, and scores
• Watch timestamp edges
• Use IN for exact lists