Skip to content
Tutorials Center
My learning

Topics

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.

SQL
SELECT title, price
FROM books
WHERE price BETWEEN 10 AND 15;

Try it

SQL Both ends are included ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

SQL Dates stored as ISO text ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

SQL Reversed bounds match nothing ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

SQL Timestamps: a date upper bound misses the last day ยท database: shop

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

SQL
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.
SQL
-- PostgreSQL
SELECT 7 BETWEEN SYMMETRIC 10 AND 5;   -- true

Usage notes

  • Inclusive on both sides. BETWEEN 10 AND 15 includes exactly 10 and exactly 15.
  • Low first. BETWEEN 15 AND 10 is an empty range; it returns no rows and no error (except with SYMMETRIC in 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, BETWEEN is 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 NULL is neither between nor not between; both BETWEEN and NOT BETWEEN drop it.
  • Indexes. BETWEEN on 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.