IN queries a list, BETWEEN queries a range and LIKE queries a pattern — three filters that read almost like English because they answer the three shapes most real-world questions take: which group, what band, and what text shape. All three have an edge case worth rehearsing before production meets it.
What does IN match, and why reach for it over a chain of ORs?
IN tests membership in an explicitly written list. The two invited departments from the last lesson collapse from a two-branched OR into one readable clause — same four people, half the text:
SELECT name, department FROM staff WHERE department IN ('Buying', 'Cafe');
name department
------------- ----------
Priya Raman Buying
Dana Kowalski Buying
Aisha Zahra Cafe
Joe Naylor Cafe
Readability is the first win; the second is that the list can arrive later from somewhere else — a subquery (lesson 27 builds exactly that) or a code-driven parameter list. Filters in application code very often take the shape WHERE id IN (...) because the user picked three rows from a screen. IN also works on numbers anywhere: WHERE id IN (1, 5, 8) is the classic quick a lookup.
How does BETWEEN set a range — are the endpoints inside or out?
BETWEEN takes a low and a high value and includes both — the salary band 25000 to 32000 keeps the barista on 25500 and the cafe lead on the very edge value 32000:
SELECT name, salary FROM staff WHERE salary BETWEEN 25000 AND 32000;
name salary
------------- ------
Marcus Bell 28000
Aisha Zahra 32000
Tom Whitfield 27500
Joe Naylor 25500
Inclusive edges are the part reviewers debate most, so either keep BETWEEN for self-explanatory ranges (whole bands, whole dates) or write the explicit form when a boundary means just before. Dates reward extra care: a deadline of end-of-day is a value such as BETWEEN '2025-01-01' AND '2025-01-31 23:59:59' in engines that timestamp, and off-by-one seconds at midnight is a quietly classic report bug.
What do the LIKE wildcards match, character by character?
LIKE is a pattern language with exactly two wildcards: percent matches any run of characters (including none), underscore matches exactly one. Pin down an email starting tom., then isolate one character mid-pattern. Both filters find their intended person:
SELECT name FROM staff WHERE email LIKE 'tom.%';
name
-------------
Tom Whitfield
-- the underscore pins position 7: priya._aman@ versus priya.raman@
SELECT name FROM staff WHERE email LIKE 'priya._aman@%';
name
-----------
Priya Raman
Percent-led patterns (%@example.org) filter by domain; percent-flanked ones scan every row and cannot lean on an index, a cost fact that returns in the performance module. When a value genuinely contains a percent or underscore — product codes such as SALE_2025 — write an ESCAPE clause so the wildcard is treated as literal text; forgetting it is why pattern filters over stock codes occasionally match a thousand harmless rows.
Anchor placement decides how much of the table it must scan: a prefix pattern such as tom.% can use any index on the column and exits early, while %ton% forces a full sweep. That single line of theory maps directly onto the dashboards that hammer your indices at month end — leading wildcards are the single most common accidental cost in reports that grew one carelessly-typed condition at a time.
Is LIKE case-sensitive?
Not here, and the difference from equality is worth asserting: lesson 5's = on this database refused lowercase london, yet LIKE folds ASCII case for every stored value:
SELECT name FROM staff WHERE email LIKE 'PRIYA%';
name
-----------
Priya Raman
The answer in other engines differs — dialects, not syntax: SQLite ignores case for ASCII letters; PostgreSQL keeps LIKE strict and offers ILIKE for the folding behaviour; MySQL folds case when the column's collation spells it that way, typically yes. Never publish a filter whose brittleness you have not tested on the engine you will actually schedule against.
What happens when NOT IN meets NULL?
NOT IN already quietly drops unknown districts from this roster — asking outside two cities yields three people, because Ruth's NULL city fails membership the usual way:
SELECT name, city FROM staff WHERE city NOT IN ('London', 'Leeds');
name city
------------- ----------
Dana Kowalski Manchester
Aisha Zahra Leicester
Joe Naylor Manchester
Now the trap: put a NULL inside the compare list. A row survives only if its value differs from every list member — and nothing differs from an unknowable value:
SELECT name, city FROM staff WHERE city NOT IN ('London', NULL);
(no rows)
Eight rows in, zero out, no error raised. Every NOT IN should be trained never to accept a list that might contain NULL — filter the list, or switch the shape to NOT EXISTS once lesson 28's tools arrive.
Quick reference
- IN = membership in a written list; beats OR ladders for three or more alternatives.
- BETWEEN is inclusive at both endpoints; say so in code comments when edges are delicate.
- LIKE: percent is any run, underscore is one character, and ESCAPE protects literal wildcards.
- A NULL hiding inside NOT IN silently starves the result to zero rows.
Set work: count the staff whose role contains the word Bookseller with one LIKE query, name everyone in the salary band 27000-30000 inclusive with BETWEEN, and list everyone whose department is not Cafe, Sales or Buying — then explain in one line what happened to the row with no city, and patch that one-line gap with a NULL-aware rewrite.