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.
IN: match against a list
IN is a shortcut for a chain of ORs on the same column. These two are identical; IN is just far easier to read as the list grows past two or three values:
| customer | ship_state |
|---|---|
| Aisyah | Selangor |
| Bala | Penang |
| Chong | Selangor |
| Farid | Penang |
Only two states
The same thing, the long way
Just to make the equivalence concrete: this produces the exact same result as the IN version above, just spelled out.
| customer | ship_state |
|---|---|
| Aisyah | Selangor |
| Bala | Penang |
| Chong | Selangor |
| Farid | Penang |
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: identical result, much better readability at scale.
IN, unrolled into ORs
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:
| customer | total |
|---|---|
| Aisyah | 120.00 |
| Aisyah | 95.00 |
Mid-range orders
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:
| customer | ship_state | total |
|---|---|---|
| Aisyah | Selangor | 120.00 |
Both filters together
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.