NULL & Three-Valued Logic

Half of the classic SQL bugs — the report that quietly lost rows, the filter that matched nothing, the total that came back empty — trace to one value and one rule. The value is NULL, and it does not mean zero or empty string: it means unknown. The rule is that SQL logic has three outcomes, not two. Most courses bury this lesson late, after it has already cost you an afternoon; here it comes early, because it explains everything weird you're about to meet.

NULL means "unknown", and it's everywhere

The café's staff table has honest gaps — a phone number never collected, a bonus not yet decided:

SELECT name, role, phone FROM staff;

 name  |  role   |  phone
-------+---------+----------
 Rosa  | owner   | 555-0101
 Ken   | manager | 555-0102
 Ada   | barista | NULL
 Tomas | barista | 555-0104
 Yuki  | baker   | NULL
 Priya | barista | 555-0106
(6 rows)

NULL is not the text "NULL", not 0, not '' — it's the database's marker for this fact is missing. (Our grids print it dimmed, and spelled out; stock psql prints an empty cell by default, which is how NULLs stay invisible until they bite. Keep that in mind when reading someone's terminal.)

The trap, in one query

Find the staff with no phone on file. The obvious query — and its result:

SELECT name FROM staff WHERE phone = NULL;

 name
------
(0 rows)

Zero rows. Not an error, not a warning — a perfectly wrong answer, delivered with total confidence. Welcome to the most important trap in SQL.

SELECT name, phone FROM staff WHERE phone IS NULL;

 name | phone
------+-------
 Ada  | NULL
 Yuki | NULL
(2 rows)

Three-valued logic: TRUE, FALSE, UNKNOWN

Here's the rule that turns the trap from folklore into a system you can reason with. Every SQL condition evaluates to one of three results: TRUE, FALSE, or UNKNOWN — and anything compared with NULL is UNKNOWN. You can watch it happen:

SELECT NULL = NULL AS equal, NULL <> NULL AS not_equal;

 equal | not_equal
-------+-----------
 NULL  | NULL
(1 row)

Both unknown. Now the one sentence that decodes every NULL bug you'll ever meet: WHERE keeps a row only when the condition is TRUE — FALSE is dropped, and UNKNOWN is dropped just the same. That's why = NULL matched nothing. And it's why this next query silently lies:

SELECT name, bonus FROM staff WHERE bonus <> 400.00;

 name  | bonus
-------+--------
 Ada   | 120.00
 Yuki  | 250.00
 Priya |   0.00
(3 rows)

Read it as a human — "everyone whose bonus isn't 400" — and you'd expect Rosa and Tomas, whose bonuses are undecided, to be in the list. They're not: NULL <> 400.00 is UNKNOWN, and UNKNOWN rows vanish. The grid looks completely plausible. Nothing warned you. This is SQL's signature failure mode: not an error, a silently smaller answer — and it's why the row-count footer is the first thing a professional checks. If those rows should be kept, say so explicitly: WHERE bonus <> 400.00 OR bonus IS NULL.

NULL poisons expressions, too

Comparisons aren't special — NULL propagates through arithmetic and concatenation the same way: unknown in, unknown out. Compute monthly pay as hours × rate + bonus:

SELECT name, hourly_rate, bonus,
       hourly_rate * 160 + bonus AS month_pay
FROM staff;

 name  | hourly_rate | bonus  | month_pay
-------+-------------+--------+-----------
 Rosa  |        0.00 |   NULL |      NULL
 Ken   |       28.00 | 400.00 |   4880.00
 Ada   |       19.50 | 120.00 |   3240.00
 Tomas |       19.50 |   NULL |      NULL
 Yuki  |       22.00 | 250.00 |   3770.00
 Priya |       18.00 |   0.00 |   2880.00
(6 rows)

One unknown input and the whole calculation is unknown — arithmetically honest, practically useless for a payroll report.

Taming NULL: COALESCE and NULLIF

COALESCE(a, b, …) returns the first non-NULL argument — the standard way to say "and if it's missing, use this." Treat an undecided bonus as zero:

SELECT name,
       hourly_rate * 160 + COALESCE(bonus, 0) AS month_pay
FROM staff;

 name  | month_pay
-------+-----------
 Rosa  |      0.00
 Ken   |   4880.00
 Ada   |   3240.00
 Tomas |   3120.00
 Yuki  |   3770.00
 Priya |   2880.00
(6 rows)

Its mirror-image partner NULLIF(a, b) returns NULL when a = b — "treat this value as missing." Its killer app is protecting division: total / NULLIF(count, 0) turns a crash into a NULL (6.3 puts it to work). Together they're your conversion layer between unknown and usable: COALESCE on the way out to humans, NULLIF on the way in from messy data.

Try all of it — and the prompt below remembers your experiments:

NULL will follow you through this course the way it follows every database — from now on, you'll see it coming. Next: CASE, the expression that lets a query make decisions.