SQL / SELECTING ROWS
Ranges with BETWEEN and where the edges fall
Use BETWEEN for inclusive ranges, spot reversed bounds, and switch to a half-open range when the column stores timestamps.
What you will learn
- Read x BETWEEN a AND b as x >= a AND x <= b, with both edges inside the range
- Put the low bound first: reversed bounds return zero rows and raise no error
- Use >= start AND < next start for timestamp columns instead of BETWEEN
- Know that a NULL value matches neither BETWEEN nor NOT BETWEEN
Understanding Ranges with BETWEEN and where the edges fall
BETWEEN is not a new kind of comparison. The engine rewrites x BETWEEN a AND b into x >= a AND x <= b, so both edges belong to the range: a closed interval. Reading celsius BETWEEN 18.0 AND 22.0 as the interval [18.0, 22.0] tells you at a glance that a reading of exactly 22.0 is returned while 22.1 is not. The practical gain is that the tested value is written once, which matters when it is a long expression rather than a bare column name.
Because that rewrite is mechanical, nothing reorders the two bounds for you. Writing BETWEEN 22.0 AND 18.0 produces celsius >= 22.0 AND celsius <= 18.0, a condition no single value can satisfy, and the result is an empty set with no warning attached. BETWEEN is directional: the left bound is the floor and the right bound is the ceiling, always in that order.
The edge that catches people is time. A date-shaped literal compared against a timestamp column is widened to midnight, so BETWEEN '2026-03-01' AND '2026-03-31' really stops at 2026-03-31 00:00:00 and silently discards almost a full day of rows. Closed intervals suit values you can enumerate, such as integers or whole dates, because you can name the last one. A timestamp range has no nameable last instant before the next day starts, so time spans want a half-open shape: at least the start, strictly less than the next start.
CREATE TABLE reading (
id integer,
celsius numeric(4,1)
);
INSERT INTO reading (id, celsius) VALUES
(1, 17.9), (2, 18.0), (3, 20.5), (4, 22.0), (5, 22.1);
SELECT id, celsius
FROM reading
WHERE celsius BETWEEN 18.0 AND 22.0
ORDER BY id;BETWEEN is shorthand for a closed interval, >= low AND <= high, so both edges are included and the low bound must be written first.
Worked examples
Reversed bounds return nothing
Shows that swapping the two bounds produces an impossible condition instead of an error.
SELECT id, celsius
FROM reading
WHERE celsius BETWEEN 22.0 AND 18.0;Example explained
Line 1The clause expands to celsius >= 22.0 AND celsius <= 18.0.
Line 2No number is at the same time at least 22.0 and at most 18.0, so every row fails.
Line 3The server reports zero rows rather than complaining, which is why a reversed range can sit unnoticed in a report for months.
The upper edge on a timestamp column
Demonstrates that a date literal against a timestamp column pins the upper edge to midnight, cutting off the last day.
CREATE TABLE event_log (
id integer,
at timestamp
);
INSERT INTO event_log (id, at) VALUES
(1, '2026-03-30 12:00:00'),
(2, '2026-03-31 00:00:00'),
(3, '2026-03-31 16:45:00'),
(4, '2026-04-01 00:00:00');
SELECT id, at
FROM event_log
WHERE at BETWEEN '2026-03-01' AND '2026-03-31'
ORDER BY id;Example explained
Line 1The column is timestamp, so the literal '2026-03-31' is read as 2026-03-31 00:00:00.
Line 2Row 2 sits exactly on that upper edge and is kept, which confirms the edge is inclusive.
Line 3Row 3 happens at 16:45 on the same calendar day but is greater than the upper edge, so it disappears from the result.
Line 4Row 4 is the first instant of April and is correctly outside the range.
Half-open range over the same rows
Replaces BETWEEN with a lower bound and a strict upper bound to cover every instant of March in the event_log table above.
SELECT id, at
FROM event_log
WHERE at >= '2026-03-01' AND at < '2026-04-01'
ORDER BY id;Example explained
Line 1at >= '2026-03-01' fixes the lower edge at the first instant of March.
Line 2at < '2026-04-01' stops immediately before April begins, so all of 31 March is covered including 16:45.
Line 3This shape cannot be expressed with BETWEEN, which only ever builds a closed interval.
Line 4The column stays bare on the left of both comparisons, so an index on at is still usable for the scan.
Important notes
PostgreSQL offers BETWEEN SYMMETRIC, which sorts the two bounds before comparing, but MySQL, SQL Server and SQLite have no equivalent, so do not build the habit.
Casting the column, as in at::date BETWEEN '2026-03-01' AND '2026-03-31', fixes the edge but wraps the column in a function call, which prevents a plain index on at from being used for the range.
Common mistakes
Writing price BETWEEN 100 AND 50 because that is the order the numbers were mentioned; the query returns zero rows and the data looks empty rather than the filter looking wrong.
Using created_at BETWEEN '2026-03-01' AND '2026-03-31' on a timestamp column; every event after midnight on the 31st is dropped, so a monthly total is short by nearly a full day of activity.
Using name BETWEEN 'a' AND 'z' to catch words starting with any letter; 'zebra' sorts after 'z' and is excluded, and which rows match at all depends on the collation and case of the stored text.
Try it yourself
Change, predict, then run
Create a table with a timestamp column and four rows straddling midnight at the end of April 2026, then run BETWEEN '2026-04-01' AND '2026-04-30' and >= '2026-04-01' AND < '2026-05-01' against it. Note exactly which rows only the second version finds.
Open the SQL workspaceCheck your understanding
A shipments table has a shipped_at timestamp column with rows spread through every hour of June 2026. WHERE shipped_at BETWEEN '2026-06-01' AND '2026-06-30' returns far fewer rows than the month contains. What is happening?
- BETWEEN excludes its endpoints, so the 1 June and 30 June rows are both dropped
- The bounds are the wrong way round and need to be swapped
- '2026-06-30' is read as 2026-06-30 00:00:00, so shipments later that day sit above the upper edge
- Comparing a timestamp with a text literal yields NULL, so 30 June rows are filtered out
Show answer
BETWEEN builds a closed interval, so the endpoints are included and the first option has the behaviour backwards: rows at exactly 2026-06-01 00:00:00 and 2026-06-30 00:00:00 do match. The loss comes from the cast, since a date-shaped literal against a timestamp column becomes midnight, ending the range almost 24 hours before June does. Writing shipped_at >= '2026-06-01' AND shipped_at < '2026-07-01' covers the whole month.