SQL guide · Filtering

SQL BETWEEN: Select Values Within a Range

The BETWEEN operator tests whether a value falls inside a range. price BETWEEN 10 AND 20 is shorthand for price >= 10 AND price <= 20, so both the low and the high bounds are included.

It works on numbers, dates and text. For text, the range follows the collation sort order, which can give unexpected results with mixed case or accents. For dates and timestamps there is an extra trap: a bound written as a date is read as midnight, so an end date silently excludes later times on that day.

The inverse, NOT BETWEEN, returns values below the lower bound or above the upper bound. The lower bound must come first; if you reverse the two values, standard SQL returns no rows rather than swapping them for you.

Basic syntax

SELECT column1
FROM table_name
WHERE column BETWEEN low_value AND high_value;

WHERE column NOT BETWEEN low_value AND high_value;

low_value and high_value are inclusive and must be the same or compatible types as the column. The AND here is part of the BETWEEN syntax, not a separate logical operator, so extra conditions need their own AND. Because the operator is inclusive on both sides, adjacent ranges such as 0 to 10 and 10 to 20 both capture the value 10, so use a half-open range when you need buckets that do not overlap.

SQL BETWEEN examples

Numeric range

Products priced from 10 to 50, including both 10 and 50. Equivalent to two comparisons joined by AND.

SELECT product_id, name, price
FROM products
WHERE price BETWEEN 10 AND 50;

Date range

Orders placed in January 2026. This works cleanly when order_date is a date column with no time portion.

SELECT order_id, order_date, total
FROM orders
WHERE order_date BETWEEN '2026-01-01' AND '2026-01-31';

Safer range for timestamps

For timestamp columns, use a half-open range so rows late on the final day are not lost. This catches all of January regardless of the time of day.

SELECT order_id, created_at
FROM orders
WHERE created_at >= '2026-01-01'
  AND created_at <  '2026-02-01';

NOT BETWEEN and text ranges

The first query excludes a mid-range of salaries. The second returns last names starting with letters A through M, noting that a name beginning with a later letter is outside the range.

SELECT name, salary
FROM employees
WHERE salary NOT BETWEEN 40000 AND 80000;

SELECT name
FROM employees
WHERE last_name BETWEEN 'A' AND 'N';

Where SQL dialects differ

PostgreSQL: Supports BETWEEN SYMMETRIC, which swaps the bounds if they are in the wrong order. Timestamp literals without a time mean midnight.

MySQL: String ranges depend on the collation, which is case insensitive by default. Comparing a DATETIME to a date string treats the date as midnight.

SQL Server: The datetime type has about 3 millisecond precision, so an end bound like '23:59:59.999' can round up to the next day. Use a half-open range instead.

SQLite: Dates are stored as text, real or integer values, so BETWEEN works reliably only when they use a sortable format such as ISO 8601 (YYYY-MM-DD).

Oracle: A DATE column always has a time portion, so BETWEEN with a bare end date excludes later times that day. Use TRUNC() or a half-open range.

Common mistakes

Putting the larger value first. BETWEEN 50 AND 10 returns no rows in standard SQL.
Using a date as the upper bound on a timestamp column and losing rows after midnight on that date.
Forgetting that both bounds are inclusive, causing double counting when two adjacent ranges share an edge value.
Applying BETWEEN to text and expecting numeric order. '10' sorts before '9' as text.

Related SQL guides

Browse all SQL guides

Need help writing the SQL?

Describe your tables and the result you want, then review the generated query against your database before running it.

Try the AI SQL generator