BETWEEN

Overview

expr BETWEEN low AND high is shorthand for expr >= low AND expr <= high -- it's inclusive on both ends. It works with numbers, dates, timestamps, and any other orderable type, and it reads more naturally than the equivalent compound condition for simple range checks.

Copy code
SELECT order_id, order_total
FROM orders
WHERE order_total BETWEEN 50 AND 200;

SELECT event_name, event_date
FROM events
WHERE event_date BETWEEN DATE '2026-01-01' AND DATE '2026-03-31';

NOT BETWEEN

Negating the range is just as readable:

Copy code
SELECT * FROM orders WHERE order_total NOT BETWEEN 50 AND 200;

Common pitfall: date ranges

A frequent mistake is using BETWEEN with a bare date on one side and a TIMESTAMP column on the other, expecting it to cover the whole end day. col BETWEEN '2026-01-01' AND '2026-01-31' on a TIMESTAMP column effectively means ... AND '2026-01-31 00:00:00', silently excluding any timestamps later in that final day. The safer pattern is an explicit half-open range:

Copy code
SELECT * FROM events
WHERE event_ts >= TIMESTAMP '2026-01-01 00:00:00'
  AND event_ts <  TIMESTAMP '2026-02-01 00:00:00';

BETWEEN SYMMETRIC (DuckDB)

DuckDB supports BETWEEN SYMMETRIC, which works even if the "low" and "high" bounds are supplied in the wrong order -- it automatically treats whichever value is smaller as the lower bound:

Copy code
SELECT 5 BETWEEN SYMMETRIC 10 AND 1;  -- TRUE, equivalent to 5 BETWEEN 1 AND 10

Performance

Because BETWEEN decomposes into two comparisons, it can use the same indexes or zone-map pruning that a range scan on >=/<= would use -- there's no performance penalty for using BETWEEN over the equivalent AND expression.