Filtering with WHERE
How to keep only the rows you care about — comparisons, ranges, lists, patterns, and the logic that combines them.
What you'll learn
- The comparison operators you'll use every day
- BETWEEN, IN, and LIKE — what each is really doing
- How AND, OR, NOT combine, and why parentheses matter
- Where NULL quietly breaks a filter
Before you start
Almost no query you write at work wants the whole table. It wants this customer, that month, those products. WHERE is how you say which rows to keep: it runs once per row, evaluates a true/false test (a predicate), and keeps only the rows that come out true.
We will filter this small product table all lesson:
| id | name | category | price |
|---|---|---|---|
| 1 | Pencil | Stationery | 2 |
| 2 | Notebook | Stationery | 6 |
| 3 | Coffee | Grocery | 9 |
| 4 | Laptop | Electronics | 900 |
| 5 | Cable | Electronics | 12 |
Comparisons
The operators are what you would guess: =, <> (or !=), <, <=, >, >=. Strings compare alphabetically, dates chronologically, numbers numerically.
SELECT name, price
FROM products
WHERE price > 10;| name | price |
|---|---|
| Laptop | 900 |
| Cable | 12 |
BETWEEN, IN, and LIKE
BETWEEN a AND b is shorthand for >= a AND <= b, and both ends are included — a frequent surprise. IN (...) checks membership in a list, far cleaner than chaining ORs. And LIKE matches a text pattern, where % stands for any run of characters and _ for exactly one:
SELECT name, category, price
FROM products
WHERE name LIKE 'C%'; -- starts with C| name | category | price |
|---|---|---|
| Coffee | Grocery | 9 |
| Cable | Electronics | 12 |
For dates, the half-open range >= '2024-03-01' AND < '2024-04-01' is the idiom analysts prefer over BETWEEN, because it composes cleanly across month and year boundaries without you ever having to remember how many days a month has.
Combining filters — and the precedence trap
You join conditions with AND, OR, and NOT. The one rule that trips people: AND binds tighter than OR, so A OR B AND C means A OR (B AND C). When in doubt, parenthesise.
SELECT name, category, price
FROM products
WHERE (category = 'Stationery' OR category = 'Grocery')
AND price < 10;| name | category | price |
|---|---|---|
| Pencil | Stationery | 2 |
| Notebook | Stationery | 6 |
| Coffee | Grocery | 9 |
Drop those parentheses and the query quietly changes meaning to “all Stationery, plus Grocery under 10” — every pencil and notebook regardless of price. Same words, different answer, real bug.
Practice
Quick check
Practice this in an interview
All questionsRaw-column filters belong in WHERE so the engine scans fewer rows before grouping. Aggregate filters must go in HAVING. Applying a filter in HAVING that could have been in WHERE forces the engine to aggregate more rows than necessary.
AND binds more tightly than OR, just like multiplication binds more tightly than addition. Without parentheses, the expression A OR B AND C is evaluated as A OR (B AND C), not (A OR B) AND C. Always use parentheses when mixing AND and OR to make intent explicit.
WHERE filters individual rows before any grouping occurs; HAVING filters groups after GROUP BY is evaluated. You must use HAVING when the filter condition references an aggregate function like SUM or COUNT.
Use IN for a discrete set of values; use BETWEEN for a contiguous range. BETWEEN is inclusive on both ends. IN does not imply any order and is ideal for filtering against a known list, while BETWEEN is cleaner for numeric or date ranges.
LIKE matches string patterns using % (any sequence of characters) and _ (exactly one character). A leading wildcard like '%smith' forces a full table scan because the index cannot be used from the left side; a trailing wildcard like 'smith%' can use a B-tree index prefix scan.
WHERE filters individual rows before any grouping or aggregation occurs; HAVING filters groups after aggregation. You cannot reference an aggregate function like COUNT() or SUM() in a WHERE clause because those values don't exist yet at that stage of query execution.