IN, BETWEEN and LIKE: shorter conditions
IN checks a value against a list, BETWEEN checks a range (both ends included), and LIKE matches text against a pattern with % (any number of characters) and _ (exactly one).
Why it matters
status = 'shipped' OR status = 'pending' OR status = 'cancelled' is long and easy to get wrong. Questions like "names starting with P" or "orders in February" come up all the time. These three words make such conditions short and readable.
The idea
IN: status IN ('shipped', 'pending') is true when status equals any value in the list.
BETWEEN: amount BETWEEN 50 AND 100 means amount >= 50 AND amount <= 100, ends included.
LIKE: product_name LIKE 'P%' matches text starting with P; % stands for any number of characters (even none) and _ for exactly one.
Put NOT in front of any of them to flip it: NOT IN, NOT BETWEEN, NOT LIKE.
IN is a guest list: is your name on it? BETWEEN is a height limit at a fair ride: between 1.2 m and 1.9 m, both ends allowed. LIKE is a crossword clue with blanks: "P _ _" fits Pen.
A guest list doesn't care about missing names, but SQL does: if the list after NOT IN contains a NULL, nothing matches at all. Unit 4 shows this trap.
Syntax
WHERE column IN (value1, value2)
WHERE column BETWEEN low AND high
WHERE text_column LIKE 'pattern%'How it works
status IN ('shipped', 'pending')is checked against each order: shipped matches, pending matches, delivered doesn't.order_date BETWEEN '2024-02-01' AND '2024-02-29'keeps every date from the 1st to the 29th of February, both days included.product_name LIKE 'P%'keeps Pen and Phone case: P, then anything.LIKE '%s'keeps names ending in s: Headphones.LIKE '_en'means exactly one character, then "en": Pen matches, but Phone case doesn't.
Key points
x IN (a, b)meansx = a OR x = b.x BETWEEN a AND bmeansx >= a AND x <= b— both ends included, and a must be the smaller value.- LIKE wildcards:
%is any number of characters (including none);_is exactly one character. - NOT IN, NOT BETWEEN and NOT LIKE flip each test.
- A LIKE pattern that starts with % ('%son') can't use a normal index, so it is slow on big tables.
Common mistakes
- Using BETWEEN on timestamps:
BETWEEN '2024-02-01' AND '2024-02-29'misses everything after midnight on the 29th. Use>= '2024-02-01' AND < '2024-03-01'instead. - Writing BETWEEN with the bigger value first:
BETWEEN 100 AND 50returns nothing. - Forgetting the % in LIKE:
LIKE 'P'only matches the exact text P.
Not true: LIKE 'P' matches every value that starts with P.
In fact: Without a wildcard, LIKE matches only the exact text P. Write LIKE 'P%' for the values that start with P.
In the interview
IN tests membership in a list, and is shorthand for several ORs. BETWEEN tests an inclusive range. LIKE matches text patterns, where percent is any sequence of characters and underscore is exactly one. Two cautions: for timestamps I use a half-open range — greater-or-equal the start and less-than the next day — rather than BETWEEN, and a leading-wildcard LIKE can't use a B-tree index.
Part of Unit 1: Reading data: SELECT, WHERE and sorting in the free SQL course.