Chapter 1 · 7 min
Filtering rows with WHERE
WHERE is the clause that decides which rows qualify. It sits between FROM and the rest of the
query, and its predicates are evaluated against each candidate row before anything is grouped,
sorted, or returned. That position matters: a filter written in the wrong clause changes the
meaning of the answer, not just its size.
SELECT order_id, customer_id, order_date
FROM orders
WHERE status = 'paid'
AND order_date >= DATE '2026-02-01';
The comparison operators are the ordinary ones (=, <>, <, <=, >, >=), and string
patterns use LIKE with % and _ as wildcards. IN tests membership in a list, and BETWEEN
tests an inclusive range. All of them are predicates, so they belong in WHERE; none of them
belongs in the select list.
Combining predicates
AND binds tighter than OR. That precedence is the source of a classic interview mistake:
-- Returns paid orders from February, plus every cancelled order ever.
SELECT order_id
FROM orders
WHERE status = 'paid' AND order_date >= DATE '2026-02-01'
OR status = 'cancelled';
The second line is not part of the date restriction, because OR is evaluated after AND. If the
intent is “paid orders in February, or cancelled orders in February”, write the grouping explicitly:
WHERE order_date >= DATE '2026-02-01'
AND (status = 'paid' OR status = 'cancelled');
Parentheses are cheap and they make the intent legible. An interviewer reading the first query cannot tell whether the precedence was intended or accidental.
Patterns and ranges
LIKE is case-sensitive in Postgres; ILIKE is the case-insensitive variant. Leading wildcards
(LIKE '%shirt') usually prevent an index from being useful, which is worth mentioning when a
question asks about performance. IN with a long literal list behaves similarly: it is a filter,
not a lookup table, so it grows with the list.
BETWEEN includes both endpoints. For a date column that means BETWEEN '2026-01-01' AND '2026-01-31' covers the whole of January only if the column is a date. On a timestamp column,
the last day is cut off at midnight, so the half-open form >= '2026-01-01' AND < '2026-02-01' is
the safer habit.
What to say out loud
When a filtering question comes up, state the predicate you are applying, the column type it
applies to, and whether the boundary is inclusive. That sentence is the answer; the SQL is just its
transcription. If the question mentions missing values, say how NULL behaves under the predicate
before writing it — the next lesson covers that directly.