SQL BETWEEN: inclusive number and date ranges

The SQL BETWEEN operator filters rows whose value falls inside an inclusive range, most often a span of numbers or two dates. It collapses a pair of >= and <= comparisons into one readable predicate, and NOT BETWEEN inverts it to match values outside the range. BETWEEN works with numbers, text, and dates, though datetime columns need care around the time component.

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.

SQL BETWEEN Two Dates

Selecting rows between two dates is the most common use of BETWEEN. The same orders table, filtered to a date window:

SELECT customer, placed FROM orders
WHERE placed BETWEEN '2026-02-10' AND '2026-02-20';

Output:

+----------+------------+
| customer | placed     |
+----------+------------+
| Alma     | 2026-02-10 |
| Ben      | 2026-02-15 |
| Cara     | 2026-02-20 |
+----------+------------+
3 row(s)

Both bounds are inclusive, so the orders on 2026-02-10 and 2026-02-20 are both kept. For a DATE column this is exact.

The trap is DATETIME/timestamp columns. '2026-02-20' is read as 2026-02-20 00:00:00, so an order placed at 14:30 that day falls after the upper bound and is missed. Capture the full day with a half-open interval instead:

WHERE placed >= '2026-02-10' AND placed < '2026-02-21';

Always write date literals in YYYY-MM-DD form so the database parses them unambiguously across configurations.

What Is SQL BETWEEN?

SQL BETWEEN tests whether a value falls within an inclusive range: column >= low AND column <= high. Both endpoints are part of the result, and there is no built-in exclusive variant. Some databases also support BETWEEN SYMMETRIC, which swaps the bounds automatically when the higher value is written first.

Common Mistakes With SQL BETWEEN

Mistake: Reversing the bounds.

Wrong:

WHERE total BETWEEN 150 AND 50

Right:

WHERE total BETWEEN 50 AND 150

Why: plain BETWEEN requires the lower value first. Reversed bounds match nothing, unless the engine supports BETWEEN SYMMETRIC.

Mistake: Expecting an exclusive upper bound.

Wrong:

WHERE id BETWEEN 1 AND 100   -- assumes 100 is excluded

Right:

WHERE id >= 1 AND id < 100

Why: BETWEEN always includes both ends. When the top value must be excluded, as with pagination or datetime spans, use >= with <.

BETWEEN vs NOT BETWEEN vs IN

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

Performance Notes

BETWEEN is sargable, so 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.

BETWEEN in SQL Server, MySQL, Oracle, and PostgreSQL

BETWEEN is standard SQL, so the same column BETWEEN low AND high syntax and inclusive behavior hold in SQL Server, MySQL, PostgreSQL, Oracle, and SQLite. The only real difference is how each engine stores dates.

Oracle’s DATE type always carries a time component, so even a column you think of as a plain date triggers the datetime trap: a bound of '2026-02-28' means midnight, and later rows that day drop out. SQL Server datetime and datetime2 behave the same way. Use the half-open >= start AND < next_day pattern on any timestamp column regardless of engine.

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.

How do I select rows between two dates in SQL?

Use WHERE col BETWEEN 'start' AND 'end' with YYYY-MM-DD literals. Both dates are included. If the column stores a time component, switch to a half-open interval (>= start AND < next_day) so rows later on the end date are not dropped.

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 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.

How do I count the days between two dates in SQL?

That is a different operation from BETWEEN. BETWEEN filters rows inside a range; to count the days separating two dates, use a date-difference function: julianday(end) - julianday(start) in SQLite, DATEDIFF(day, start, end) in SQL Server, or end - start in PostgreSQL.