Chapter 06 · basic · 6 min
IN & BETWEEN: matching sets and ranges
You now know AND/OR. Two situations come up so often that SQL gives them their own dedicated shortcuts: matching a column against a list of acceptable values, and matching it against a numeric or date range.
We'll filter orders by shipping state and by total.
The dataset
An orders table with a ship_state column added, alongside the usual total.
Schema
| id | INT |
| customer | TEXT |
| total | NUMERIC |
| ship_state | TEXT |
Example data
IN: match against a list
IN is a shortcut for a chain of ORs on the same column. IN is just far easier to read as the list grows past two or three values.
Before you run it — of the 6 orders, how many ship to either Selangor or Penang?
The same thing, the long way
IN is shorthand; here's the longhand OR chain it stands in for.
Now imagine filtering against 15 states instead of 2. The OR chain becomes hard to read and easy to typo, while the IN list stays just as clear. That's the whole case for IN: much better readability at scale.
Before you run it — does this OR chain return the same rows as the IN version above, or different ones?
BETWEEN: match a range
BETWEEN a AND b matches values from a to b, inclusive on both ends, so BETWEEN 50 AND 200 includes both 50 and 200 themselves. It works on numbers, dates, and timestamps alike.
Before you run it — which of the 6 orders have a total between 50 and 200 inclusive?
Combining both
IN and BETWEEN combine with AND/OR exactly like any other condition. This narrows down to orders shipped to one of two states, in a mid-range price band.
Before you run it — combining both filters, how many orders do you think survive?
The equivalents, side by side
| Shortcut | Longhand equivalent | When to reach for it |
|---|---|---|
col IN (a, b, c) | col = a OR col = b OR col = c | 3+ values to match |
NOT col IN (a, b) | col <> a AND col <> b | excluding a short list |
col BETWEEN a AND b | col >= a AND col <= b | a numeric or date range |
If you've used Excel or Google Sheets, IN is the same idea as checking a value against a list with MATCH(), and BETWEEN is exactly a Sheets filter condition like "is between 50 and 200".
Ready to practice? AirAsia's routes question below is built around this exact pattern.
IN and BETWEEN are readability tools first: anything they do, OR and AND/>=/<= chains can also do, just more verbosely. Next: matching text patterns instead of exact values, with LIKE.