📏BETWEEN and IN

Sugar for ranges and finite sets

BETWEEN a AND b is inclusive on both ends in the SQL standard, always confirm midnight boundaries for timestamps. IN (…) expands to a disjunction of equalities; IN (SELECT …) is a semi-join shape that optimizers treat differently from huge literal lists.

NOT IN (subquery) when inner rows can be NULL

If the subquery returns even one NULL member, classical SQL makes the whole NOT IN predicate UNKNOWN for many rows, often “no rows returned” surprises.

NOT EXISTS or INNER join anti-pattern

Prefer WHERE NOT EXISTS (SELECT 1 FROM …) with an inner WHERE that excludes NULL keys, or rewrite as an anti-join with explicit null guards.

SQL

BETWEEN on dates (inclusive endpoints)

SELECT batch_id, manufactured_on
FROM vaccine_batches
WHERE manufactured_on BETWEEN DATE '2026-01-01' AND DATE '2026-03-31';

✓Illustrative result set

batch_id	manufactured_on
VB-1001	2026-01-12
VB-1009	2026-03-30

SQL

IN with literals

SELECT lot_number, qc_status
FROM reagent_lots
WHERE qc_status IN ('HOLD', 'FAIL');

✓Illustrative result set

lot_number	qc_status
L-2201	HOLD
L-2204	FAIL
L-2209	HOLD

SQL

IN with subquery

SELECT patient_id
FROM appointments
WHERE clinician_id IN (
  SELECT clinician_id FROM clinicians WHERE specialty = 'Cardiology'
);

✓Illustrative result set

patient_id
501
512
533

⚙️Engine realities

Ranges on leading index columns become scans or seeks; giant IN lists hurt parse memory and plan cache.

For thousands of IDs from a file, load a temp table or use an array parameter, do not concatenate a megabyte IN string.
For time windows that should not double-count midnight, prefer half-open ranges: >= start AND < end instead of BETWEEN when your team agrees that convention.

Timezone checklist

Normalize event timestamps to UTC before applying BETWEEN on dashboards that aggregate “per calendar day” across sites.

TL;DR

BETWEEN a AND b is inclusive on both ends in standard SQL, watch off-by-one on timestamps; many teams prefer half-open ranges (>= start AND < end) for time windows. IN (…) is shorthand for = against a fixed list or subquery; huge literal lists hurt parse time and plan cache, use a temp table or join to a driving set instead.