P01 · SQL & Querying
SQL WHERE, explained and its silent traps
How SQL's WHERE clause really works — and the classic trap that makes a filter return zero rows with no error at all.
Start with the basics
Filtering is how you tell a database which rows you want back. You add a WHERE clause with a condition, and the query keeps only the rows that make that condition true — everything else is left out.
A condition is just a yes/no test applied to each row: amount is over 100, country equals US, the sign-up date is on or after some day. You can join several tests together with AND and OR to be more specific.
That is the whole idea, and it feels obvious. Which is exactly why it is easy to trust a WHERE clause in the one case where it quietly does something you did not mean.
One that works
| id | amount |
|---|---|
| 1 | 120 |
| 2 | 80 |
| 3 | 200 |
SELECT id, amount FROM orders
WHERE amount > 100
ORDER BY id;| id | amount |
|---|---|
| 1 | 120 |
| 3 | 200 |
Only the two rows whose amount is over 100 come back — order 2 is left out.
WHERE keeps only the rows where the condition is TRUE.
When to use it
- Reach for WHERE whenever you want a subset of a table: today's orders, active users, or rows above a threshold.
- It runs before grouping and totals, so it decides which rows even get counted.
- If you keep pulling the whole table and filtering it later in your app, that is usually a WHERE clause waiting to happen.
That one sentence is also where filtering quietly lies. A condition in SQL is not only true or false — it can be UNKNOWN, and UNKNOWN is not TRUE, so those rows silently vanish. Here is the day that bites you.
See it happen
The filter that returns zero rows — NOT IN meets NULL
You want the customers who have never placed an order. There are two rows in orders — one real customer, and one order whose customer_id is NULL. Watch what NOT IN does with that NULL.
| id | name |
|---|---|
| 1 | Ada |
| 2 | Bo |
| 3 | Cy |
| customer_id |
|---|
| 1 |
| NULL |
NOT IN — looks right, returns nothing
SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders)
ORDER BY name;0 rows
NOT EXISTS — NULL-safe
SELECT name FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.id
)
ORDER BY name;| name |
|---|
| Bo |
| Cy |
0 rows, no error — the “customers we’ve never converted” report says there are none.
Walk the full example in the unit →The rest of the family
More ways a filter goes quietly wrong
A “not equal” test on a column that allows NULLs silently leaves out the NULL rows — the test is UNKNOWN, not TRUE, so those rows are excluded.
A BETWEEN range on a timestamp stops at midnight of the end date, so the last day's daytime rows are excluded; a half-open range includes them.
LIKE matches wildcards and its case sensitivity depends on the database; equals is an exact match.
Comparing a string to a number or a date forces a silent conversion that can sort or filter the rows wrongly.
Without parentheses, AND groups before OR — so mixing the two can match rows you did not intend.
Self-check
How to spot it in your own SQL
- A report that should clearly list some rows comes back empty, or with fewer rows than you expect — and no error.
- You are filtering on a column that is allowed to be NULL (status, a foreign key, an optional field).
- You wrote NOT IN with a subquery, or a “not equal” test on a nullable column.
- You filtered a date or timestamp range with BETWEEN and the last day looks short.
What people miss
Most people write NOT IN and “status not equal to X” for years before a NULL bites them in production — because nothing errors, the report just quietly lies. The fix is small once you can see it; the hard part is knowing when to look.
Go deeper
Ask Colearn
Instant answers, grounded in the same verified material the diagnostic grades against.
Why does my NOT IN return no rows?
Almost always: the subquery inside NOT IN contains a NULL. NOT IN against a list that holds a NULL can never be TRUE — for every row it becomes UNKNOWN, and UNKNOWN rows are filtered out, so you get zero rows with no error.
Rewrite it with NOT EXISTS, which is NULL-safe: it checks each outer row against the subquery directly and stays correct even when the inner set has NULLs.
from the unit →Why is my WHERE status != 'refunded' dropping rows?
Because the test is UNKNOWN — not TRUE — for every row where status IS NULL, and UNKNOWN rows are excluded. So rows with no status silently disappear from the result.
If you want those rows too, say so explicitly by also allowing the NULL case in the condition.
from the unit →Why does BETWEEN miss the last day of the range?
On a timestamp column, BETWEEN a start date AND an end date stops at midnight of the end date — so everything that happened during the daytime of that last day is excluded.
Use a half-open range instead: greater-than-or-equal to the start and strictly-less-than the day after the end. It includes all of the last day and never double-counts a boundary.
from the unit →Why does my WHERE with AND and OR match the wrong rows?
AND binds tighter than OR. So a condition like “a = 1 OR a = 2 AND b = 3” is read as “a = 1 OR (a = 2 AND b = 3)”, which is probably not what you meant.
Add parentheses to force the grouping you intend, e.g. “(a = 1 OR a = 2) AND b = 3”.
from the unit →Why is my number or date filter comparing as text?
Comparing a string to a number or a date forces an implicit cast — the engine converts one side to match the other, and the result can order or filter wrongly. As text, “100” sorts before “9”; as numbers it does not.
Compare like with like: cast intentionally, or store and compare the column as its real type rather than as a string.
from the unit →That’s the free taste