BETWEENQuery Techniques
Buy Me A Coffee

Test whether a value falls within an inclusive range. BETWEEN a AND b is equivalent to val >= a AND val <= b — both endpoints are included.

Tests whether a value falls within a range (inclusive), equivalent to greater-than-or-equal AND less-than-or-equal combined. Cleaner than separate comparisons and conveys intent more clearly. The inclusive nature can be surprising with dates -- BETWEEN 2024-01-01 AND 2024-01-02 includes all of January 1st plus part of January 2nd.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported all Standard inclusive BETWEEN. Works on numerics, dates, and strings (lexicographic comparison). NOT BETWEEN for exclusion. Date BETWEEN is inclusive of the end date — BETWEEN '2024-01-01' AND '2024-01-31' includes all of Jan 31 only up to midnight if the column is DATE type; if DATETIME, use < '2024-02-01' instead.
PostgreSQL ✓ Supported all Standard inclusive BETWEEN. Also supports BETWEEN SYMMETRIC (swaps bounds if first > second, so order doesn't matter). NOT BETWEEN and NOT BETWEEN SYMMETRIC available. Same timestamp end-of-day trap as MySQL.
SQL Server ✓ Supported all Standard inclusive BETWEEN. No SYMMETRIC variant. Same timestamp end-of-day trap — prefer >= / < for DATETIME ranges. BETWEEN on strings uses collation-dependent comparison.
Oracle ✓ Supported all Standard inclusive BETWEEN. No SYMMETRIC variant. DATE type in Oracle includes a time component (unlike ANSI DATE which is date-only), so date BETWEEN can have timestamp precision traps. Use TRUNC() to strip time component when needed.
SQLite ✓ Supported all Standard inclusive BETWEEN. No SYMMETRIC variant. SQLite has no dedicated DATE type — dates stored as TEXT (ISO 8601), REAL (Julian day), or INTEGER (Unix epoch). BETWEEN on TEXT dates works correctly as long as dates are in YYYY-MM-DD format (lexicographic order matches chronological order).

Details

BETWEEN is inclusive on both ends in all engines — this is a common mistake when filtering DATETIME/TIMESTAMP columns. The safe pattern for timestamp ranges is always col >= start AND col < end (exclusive upper bound). PostgreSQL's BETWEEN SYMMETRIC is unique. Oracle's DATE type secretly includes time, which makes date BETWEEN behave differently than expected.

Standard Syntax

SELECT * FROM orders WHERE amount BETWEEN 100 AND 500; SELECT * FROM events WHERE event_date BETWEEN '2024-01-01' AND '2024-03-31';

Version Support

MySQL: Since all PostgreSQL: Since all SQL Server: Since all Oracle: Since all SQLite: Since all

Per-Database Syntax & Notes

MySQL

Standard inclusive BETWEEN. Works on numerics, dates, and strings (lexicographic comparison). NOT BETWEEN for exclusion. Date BETWEEN is inclusive of the end date — BETWEEN '2024-01-01' AND '2024-01-31' includes all of Jan 31 only up to midnight if the column is DATE type; if DATETIME, use < '2024-02-01' instead.

SELECT * FROM orders WHERE amount BETWEEN 100 AND 500; SELECT * FROM orders WHERE amount NOT BETWEEN 100 AND 500; -- Date range (DATE type — safe): SELECT * FROM events WHERE event_date BETWEEN '2024-01-01' AND '2024-01-31'; -- DATETIME — use < instead to avoid midnight trap: SELECT * FROM events WHERE created_at >= '2024-01-01' AND created_at < '2024-02-01';

PostgreSQL

Standard inclusive BETWEEN. Also supports BETWEEN SYMMETRIC (swaps bounds if first > second, so order doesn't matter). NOT BETWEEN and NOT BETWEEN SYMMETRIC available. Same timestamp end-of-day trap as MySQL.

SELECT * FROM orders WHERE amount BETWEEN 100 AND 500; SELECT * FROM orders WHERE amount NOT BETWEEN 100 AND 500; -- SYMMETRIC: order doesn't matter SELECT * FROM orders WHERE amount BETWEEN SYMMETRIC 500 AND 100; -- same as 100..500

SQL Server

Standard inclusive BETWEEN. No SYMMETRIC variant. Same timestamp end-of-day trap — prefer >= / < for DATETIME ranges. BETWEEN on strings uses collation-dependent comparison.

SELECT * FROM orders WHERE amount BETWEEN 100 AND 500; SELECT * FROM orders WHERE amount NOT BETWEEN 100 AND 500; -- Safe datetime range (no midnight trap): SELECT * FROM orders WHERE created_at >= '2024-01-01' AND created_at < '2024-02-01';

Oracle

Standard inclusive BETWEEN. No SYMMETRIC variant. DATE type in Oracle includes a time component (unlike ANSI DATE which is date-only), so date BETWEEN can have timestamp precision traps. Use TRUNC() to strip time component when needed.

SELECT * FROM orders WHERE amount BETWEEN 100 AND 500; -- Oracle DATE includes time — use TRUNC to strip it: SELECT * FROM events WHERE event_date BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE) + 1;

SQLite

Standard inclusive BETWEEN. No SYMMETRIC variant. SQLite has no dedicated DATE type — dates stored as TEXT (ISO 8601), REAL (Julian day), or INTEGER (Unix epoch). BETWEEN on TEXT dates works correctly as long as dates are in YYYY-MM-DD format (lexicographic order matches chronological order).

SELECT * FROM orders WHERE amount BETWEEN 100 AND 500; -- Text dates work correctly in ISO 8601 format: SELECT * FROM events WHERE event_date BETWEEN '2024-01-01' AND '2024-03-31';