BETWEEN

BETWEEN is a shorthand for checking whether a value falls within a range. Instead of writing two separate comparisons, you write one readable condition.

Basic syntax

A numeric range

SQL

SELECT product_name, price
FROM products
WHERE price BETWEEN 10 AND 50;

This is exactly equivalent to writing the range out as two comparisons joined with AND:

What BETWEEN expands to

SQL

SELECT product_name, price
FROM products
WHERE price &gt;= 10 AND price &lt;= 50;

BETWEEN is inclusive on both ends

It is easy to assume BETWEEN 10 AND 50 excludes 10 or 50, similar to some programming language range functions. It does not — both boundary values are included in the result. If you need an exclusive bound, you have to write the comparison manually with < or > instead of BETWEEN.

BETWEEN with dates — a classic gotcha

BETWEEN works with dates just as it does with numbers, but it hides a subtle trap when the column actually stores a timestamp (date *and* time) rather than a plain date.

This silently misses data

SQL

SELECT order_id, created_at
FROM orders
WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31';

The upper bound is missing almost the entire last day

When compared against a timestamp column, the literal '2024-01-31' is interpreted as midnight — 2024-01-31 00:00:00. Any order created later that same day, say at 3:00 PM, has a timestamp of 2024-01-31 15:00:00, which is *greater* than the upper bound and gets excluded. The query silently drops most of January 31st.

The reliable fix is to avoid BETWEEN for timestamp ranges and instead use an exclusive upper bound with <, comparing against the start of the *next* day:

The correct, exclusive-upper-bound approach

SQL

SELECT order_id, created_at
FROM orders
WHERE created_at &gt;= '2024-01-01'
  AND created_at &lt; '2024-02-01';

This correctly captures every timestamp during January, no matter what time of day it was recorded — including 2024-01-31 23:59:59.