07 — BETWEEN

One line: BETWEEN low AND high matches values in a range — inclusive of both ends. It's shorthand you attach to a WHERE.

The idea in plain English

BETWEEN is a cleaner way to write a two-sided range check:

WHERE amount BETWEEN 8 AND 9
-- is exactly the same as:
WHERE amount >= 8 AND amount <= 9

Both endpoints are included — 8 and 9 both count. So BETWEEN 8 AND 9 catches 8.00, 8.99, and 9.00.

NOT BETWEEN flips it — everything outside the range, and now the endpoints are excluded:

WHERE amount NOT BETWEEN 8 AND 9
-- is the same as:
WHERE amount < 8 OR amount > 9

If you want different inclusivity than BETWEEN gives, drop it and write the comparisons manually with AND.

Syntax

-- Numbers
SELECT * FROM payment
WHERE amount BETWEEN 8 AND 9;

-- Count how many fall in the range
SELECT COUNT(*) FROM payment
WHERE amount BETWEEN 8 AND 9;     --> 439

-- Dates — must be ISO 8601 format 'YYYY-MM-DD', single-quoted
SELECT * FROM payment
WHERE payment_date BETWEEN '2007-02-01' AND '2007-02-15';

Dates go in ISO 8601: four-digit year - two-digit month - two-digit day, as a quoted string: '2007-02-01'.

The timestamp trap ⚠️ (this one bites everyone)

When your column is a timestamp (date and time) but you compare against a plain date, SQL reads that date as midnight — the very start of the day (00:00:00).

So '2007-02-14' actually means 2007-02-14 00:00:00. That has a nasty consequence:

-- Intent: "all payments through Feb 14th"
WHERE payment_date BETWEEN '2007-02-01' AND '2007-02-14';
--> returns NOTHING from the 14th!

Why: a payment at 2007-02-14 09:23:00 is greater than 2007-02-14 00:00:00, so it falls outside the range. BETWEEN includes the high endpoint, but the high endpoint is only the first instant of the 14th — everything that happened during the 14th is excluded. To capture the whole 14th with BETWEEN, you'd have to push the high bound to '2007-02-15'.

The professional fix: use a half-open range instead of BETWEEN for datetimes.

-- "All of Feb 1st through Feb 14th", safely
WHERE payment_date >= '2007-02-01'
  AND payment_date <  '2007-02-15';   -- note: < next day

This >= start AND < end (a "half-open interval" [start, end)) is the standard production pattern for date ranges. It sidesteps midnight ambiguity entirely, handles any time-of-day, and doesn't care whether the column has a time component. Reach for it whenever timestamps are involved.

Production notes

• BETWEEN on timestamps → prefer >= AND <. As above. BETWEEN is fine for integers and clean dates; for datetimes, half-open ranges save you.
• Low must be ≤ high. BETWEEN 9 AND 8 returns nothing — SQL doesn't auto-swap them. If a range mysteriously returns zero rows, check you didn't flip the bounds.
• Both endpoints inclusive — easy to forget when you actually wanted an exclusive edge. BETWEEN 0 AND 100 includes 0 and 100.
• NULLs are excluded. If the value is NULL, BETWEEN is neither true nor false, so those rows don't appear (same as any WHERE).
• Works on text too, but be careful. BETWEEN 'A' AND 'M' does an alphabetical range — rarely what you want, and results depend on collation. Usually clearer to use explicit comparisons or IN.

Challenges

All use the DVD Rental database, payment table.

Challenge 1 — Warm-up (numeric)

How many payments were between $2 and $4 (inclusive)?

SELECT COUNT(*) FROM payment
WHERE amount BETWEEN 2 AND 4;

Challenge 2 — NOT BETWEEN

Return all payments whose amount is outside the $1–$8 range (i.e. cheaper than $1 or pricier than $8).

SELECT * FROM payment
WHERE amount NOT BETWEEN 1 AND 8;

Challenge 3 — Spot the bug 🔎 (the timestamp trap)

A teammate wants every payment made during the first 14 days of February 2007. They write:

SELECT COUNT(*) FROM payment
WHERE payment_date BETWEEN '2007-02-01' AND '2007-02-14';

They're surprised the count is far lower than expected and seems to drop the 14th entirely. What's wrong, and how should they write it?

SELECT COUNT(*) FROM payment
WHERE payment_date >= '2007-02-01'
  AND payment_date <  '2007-02-15';

30-second recap