Chapter 04 · basic · 5 min
NULL: the three-valued logic trap
Before we go any further with WHERE, one landmine is worth defusing early: NULL. It doesn't mean zero, or an empty string. It means unknown. That single fact breaks comparisons in a way that trips up almost everyone at least once.
We'll use the same orders table, where one order's total hasn't been recorded yet.
The dataset
An orders table where one row (Gina's) has a NULL total: the amount simply hasn't been entered yet.
Schema
| id | INT |
| customer | TEXT |
| total | NUMERIC |
| status | TEXT |
Example data
= NULL is never true, not even for NULL
You might reach for total = NULL to find Gina's row. It won't work: comparing anything to an unknown value produces an unknown result, not true, so the row is silently excluded.
Before you run it — Gina's total really is NULL. How many rows do you think this query returns?
IS NULL: a dedicated test
SQL gives you a dedicated test for missing data: IS NULL (and its opposite, IS NOT NULL).
Before you run it — will this correctly find Gina's row, unlike the = NULL version above?
Why this bug is dangerous
The = NULL trap is nasty precisely because it doesn't error. It just quietly returns fewer rows than you meant, or none at all. A report built on WHERE total = NULL doesn't crash and get noticed; it silently ships a wrong number, and wrong-but-confident is far more expensive to catch than a loud failure. This is the single most common real-world reason a "finished" query gives a subtly incorrect answer.
If you've used Excel or Google Sheets
A blank cell in a spreadsheet is the rough equivalent of NULL, and Excel/Sheets has the same dedicated check: ISBLANK(cell), rather than comparing the cell to nothing with =. IS NULL is SQL's ISBLANK.
Before you run it — this flips the check to IS NOT NULL. How many of the four orders come back now?
The NULL-safe operators
| Operator | Meaning | Example |
|---|---|---|
IS NULL | true only when the value is unknown | total IS NULL |
IS NOT NULL | true only when the value is known | total IS NOT NULL |
=, <>, <, > | never reliably true or false against NULL, always unknown | total = NULL (always empty) |
Ready to try it yourself? GXBank's referral code question in the practice set below is built entirely around finding missing values correctly.
Keep this rule in your back pocket for everything that follows: any comparison touching a NULL (=, <>, >, even inside AND/OR, which is up next) quietly evaluates to unknown, not true or false. We'll come back to NULL in more depth later, once COALESCE and NULLIF are in your toolkit. For now: reach for IS NULL, never = NULL.