SQL Reference ยท Operators and Predicates
SQL BETWEEN
Reference for SQL BETWEEN and NOT BETWEEN: inclusive range tests for numbers, text and dates, argument order, NULL behaviour and timestamp pitfalls.
value BETWEEN low AND high tests whether value lies in a range, including both ends. It is shorthand for value >= low AND value <= high, and it works with numbers, text and dates.
The operator is easy to read but has two traps: the bounds must be written low first, and a range on timestamps often misses the last day. Both are covered below.
SELECT title, price FROM books WHERE price BETWEEN 10 AND 15;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
The last example returns 3 with BETWEEN and 5 with the half-open range: the two orders placed on 13 January are later than '2025-01-13' as a string, so BETWEEN drops them.
Syntax
expr [NOT] BETWEEN low AND high
| Part | Description |
|---|---|
expr |
The value to test. |
low, high |
The inclusive bounds, with low <= high. Any comparable expressions. |
Returns: TRUE if low <= expr <= high, FALSE if not, NULL if expr, low or high is NULL (and the other comparison does not already decide it). NOT BETWEEN is the negation: values strictly below low or above high.
Dialect differences
| Database | Notes |
|---|---|
| SQLite | Standard behaviour. Text is compared as text; dates are text in ISO format. |
| PostgreSQL | Also has BETWEEN SYMMETRIC, which swaps the bounds if they are reversed. |
| MySQL | Standard. Compares text case-insensitively under the default collation. |
| SQL Server | Standard. datetime columns have the timestamp trap described below. |
| Oracle | Standard. DATE columns contain a time part, so the same trap applies. |
-- PostgreSQL SELECT 7 BETWEEN SYMMETRIC 10 AND 5; -- true
Usage notes
- Inclusive on both sides.
BETWEEN 10 AND 15includes exactly 10 and exactly 15. - Low first.
BETWEEN 15 AND 10is an empty range; it returns no rows and no error (except withSYMMETRICin PostgreSQL). - Dates and timestamps. If the column contains a time of day,
BETWEEN '2024-03-01' AND '2024-03-15'stops at midnight of the 15th and misses later times that day. Use a half-open range:>= '2024-03-01' AND < '2024-03-16'. For date-only values,BETWEENis fine. - Text ranges.
name BETWEEN 'A' AND 'M'depends on collation;'Mira'is greater than'M'and is therefore excluded. - Floating point. Rounding can put a value just outside an edge. For decimals use exact types.
- NULL. A row whose value is
NULLis neither between nor not between; bothBETWEENandNOT BETWEENdrop it. - Indexes.
BETWEENon an indexed column becomes a range scan. Keep the column bare on the left, see sargable predicates. - Alternatives. Open or half-open ranges need explicit comparison operators.