BETWEEN SQL Compatibility | MySQL, PostgreSQL, SQL Server, Oracle, SQLite

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

SQL BETWEEN Compatibility Across Databases
Database System	Support Status	Since Version	Notes
MySQL	Native	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	Native	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	Native	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	Native	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	Native	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: Native in all listed versions PostgreSQL: Native in all listed versions SQL Server: Native in all listed versions Oracle: Native in all listed versions SQLite: Native in all listed versions

Per-Database Syntax & Notes

MySQL Native syntax

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 Native syntax

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 Native syntax

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 Native syntax

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 Native syntax

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';