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.
= 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. Run this and notice it returns zero rows, even though Gina's total really is NULL.
Returns 0 rows: this is the trap
IS NULL: the correct way to ask
SQL gives you a dedicated test for this: IS NULL (and its opposite, IS NOT NULL). Use these whenever you need to check for missing data, never = NULL:
| customer | total |
|---|---|
| Gina | NULL |
Correctly finds Gina's order
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.
The opposite check: rows with a real total
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.