SQL BETWEEN: inclusive number and date ranges

SQL BETWEEN filters rows whose column value falls within a specified range, including both endpoints. It replaces a pair of >= and <= comparisons with a single predicate, which keeps WHERE clauses shorter when the range is inclusive. BETWEEN works with numbers, dates, and text, though date ranges require extra care around time components.

SQL BETWEEN Example For Range Filtering

Output:

+----------+-------+------------+
| customer | total | placed     |
+----------+-------+------------+
| Ben      | 120.5 | 2026-02-15 |
| Cara     | 89.99 | 2026-02-20 |
+----------+-------+------------+
2 row(s)

How This Example Works

1. CREATE TABLE and INSERT set up an orders table with four rows spanning different totals and dates.
2. WHERE total BETWEEN 50 AND 150 keeps only rows where total >= 50 AND total <= 150. Both boundaries are included, so a row with exactly 50 or 150 would match.
3. Rows with totals of 45.00 and 200.00 fall outside the range and are excluded.

x BETWEEN y AND z is equivalent to x >= y AND x <= z. If either bound is NULL, the predicate evaluates to unknown and the row is filtered out.

What Is SQL BETWEEN?

SQL BETWEEN tests whether a value falls within an inclusive range: column >= low AND column <= high. For numeric ranges it reads well and behaves predictably. Some databases also support BETWEEN SYMMETRIC, which swaps the bounds when they’re reversed.

Common Mistakes With SQL BETWEEN

Mistake: Using BETWEEN for timestamp ranges.

Wrong:

WHERE placed BETWEEN '2026-02-01' AND '2026-02-28'

Right:

WHERE placed >= '2026-02-01' AND placed < '2026-03-01'

Why: If the column includes time, an end date like '2026-02-28' usually matches only the start of that day. Use a half-open interval to capture the whole day.

Mistake: Swapping the bounds and expecting results.

Wrong:

WHERE total BETWEEN 150 AND 50

Right:

WHERE total BETWEEN 50 AND 150

Why: With plain BETWEEN, the first bound must be the lower value. If the bounds are reversed, the condition can’t be true.

BETWEEN vs IN vs Comparison Operators

Rule: prefer BETWEEN for inclusive numeric ranges. Switch to explicit comparisons when one bound must be exclusive—especially with dates. Use IN for a fixed set of values; BETWEEN would match unintended values in the gaps.

Performance Notes

BETWEEN is sargable—the query planner treats it like >= AND <= and can use a B-tree index on the filtered column. Wrapping the column in a function (DATE(col), LOWER(col)) prevents index use; normalize the bounds instead. Wide ranges still scan many rows even with an index, so selectivity matters more than operator choice.

FAQ

Is SQL BETWEEN inclusive or exclusive?

BETWEEN is inclusive on both ends. WHERE x BETWEEN 5 AND 10 returns rows where x is 5, 6, 7, 8, 9, or 10. There is no built-in exclusive variant; use > and < for exclusive bounds.

Can BETWEEN be used with text values?

Yes. WHERE name BETWEEN 'A' AND 'M' returns names that sort within that range under the current collation. Results depend on the database’s collation rules and case sensitivity, so test with your specific configuration before relying on text ranges in production.

Why does BETWEEN miss records on the end date?

When the column stores a datetime (date + time), an upper bound like '2026-02-28' is often treated as the start of that day. Any row later on Feb 28 falls outside the range. Use a half-open interval (>= start AND < next_day) to capture the full day.