# SQL BETWEEN: Select Values Within a Range

- URL: https://betteranalyst.com/sql/between

SQL guide · Filtering

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

- [SQL WHERE](https://betteranalyst.com/sql/where)
- [SQL AND](https://betteranalyst.com/sql/and)
- [SQL NOT](https://betteranalyst.com/sql/not)
- [SQL IN](https://betteranalyst.com/sql/in)
- [SQL ORDER BY](https://betteranalyst.com/sql/order-by)
- [SQL LIKE](https://betteranalyst.com/sql/like)

[Browse all SQL guides](https://betteranalyst.com/sql)

## 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](https://betteranalyst.com/ai-sql-query-generator)