SQL-01-014-P · Primer — NULL Handling and COALESCE
The idea in one sentence
NULL is not zero and not an empty string — it's the database's way of saying "I don't know this value," and because you can't compare against something you don't know, NULL breaks the normal rules of =, forces you to test it with IS NULL, and gives you COALESCE to swap in a sensible default when a real value is missing.
Why it exists
Real data has holes. A customer signs up but never fills in their phone number; a product has no discount yet; an order hasn't shipped, so its ship date doesn't exist yet. The database needs one honest way to say "there's no value here," and that marker is NULL. It is deliberately different from 0 and from '' (empty string): zero is a known quantity (a $0 bonus is a real bonus of zero dollars), an empty string is a known blank, but NULL means the value is genuinely unknown or not applicable.
That single idea — "unknown" — has a surprising consequence. If you don't know Ben's bonus, then the question "is Ben's bonus equal to 0?" has no true-or-false answer; the honest reply is "I don't know." So SQL can't let NULL = 0 be TRUE, and it can't let it be FALSE either. Instead it invents a third truth value, UNKNOWN, alongside TRUE and FALSE. This is three-valued logic, and it's the whole reason NULL feels weird at first.
Because ordinary = produces UNKNOWN (never TRUE) when a NULL is involved, you need a special test — IS NULL / IS NOT NULL — to actually find the holes. And once you've found them, you often want to fill them with a default for display or math. That's COALESCE: hand it a list of values and it returns the first one that isn't NULL.
How it works
COALESCE(a, b, c, ...) walks its arguments left to right and returns the first non-NULL one, so COALESCE(bonus, 0) reads as "the bonus, or 0 if it's missing." To detect a NULL you must use IS NULL or IS NOT NULL, because bonus = NULL never returns TRUE — it returns UNKNOWN, and a WHERE clause only keeps rows whose test is TRUE, so WHERE bonus = NULL silently matches nothing. That same three-valued logic means NULL = NULL is itself UNKNOWN, not TRUE (a fact the query below proves). Here we compute a paid column that treats a missing bonus as 0, while a naive bonus = 0 test stays NULL for the unknown rows:
CREATE TABLE employees (name TEXT, bonus INTEGER);
INSERT INTO employees VALUES
('Ana', 500), ('Ben', NULL), ('Cleo', 0), ('Dara', NULL);
SELECT name,
bonus,
COALESCE(bonus, 0) AS paid, -- fill the holes with 0
(bonus = 0) AS is_zero -- NULL for unknown rows!
FROM employees;
-- name bonus paid is_zero
-- ---- ----- ---- -------
-- Ana 500 500 (500 = 0 is FALSE -> 0, shown here as blank)
-- Ben 0 (bonus is NULL -> paid = 0, is_zero = NULL)
-- Cleo 0 0 1 (0 = 0 is TRUE)
-- Dara 0 (bonus is NULL -> paid = 0, is_zero = NULL)
SELECT COUNT(*) AS missing FROM employees WHERE bonus IS NULL; -- missing = 2Notice Ben and Dara: COALESCE(bonus, 0) turns their unknown bonus into a displayable 0, but bonus = 0 for them evaluates to NULL (unknown), not TRUE — which is exactly why IS NULL (not = NULL) is the only reliable way to count them.
The one gotcha
The trap is expecting NULL to behave like a normal value in comparisons and arithmetic — it poisons everything it touches. bonus = NULL is not a syntax error; it just quietly evaluates to UNKNOWN, so WHERE bonus = NULL returns zero rows and you conclude "there are no missing bonuses" when in fact there are. Even NULL = NULL is UNKNOWN, so two unknowns are never "equal." Arithmetic is just as contagious: NULL + 100 is NULL, and salary + bonus becomes NULL for anyone whose bonus is missing — one hole in one column silently blanks out the whole calculation. Aggregates add their own twist: SUM and AVG skip NULLs (so AVG divides by the count of non-NULL rows, not by every row), while COUNT(*) counts all rows but COUNT(bonus) counts only the non-NULL ones — a mismatch that quietly shifts your averages. The fix is a habit, not a trick: to find NULLs use IS NULL / IS NOT NULL, never = or <>; and to neutralize NULLs before math or display, wrap the column in COALESCE — COALESCE(salary, 0) + COALESCE(bonus, 0) — so a missing part contributes a real 0 instead of erasing the total.
See it — how a comparison resolves when NULL is involved
flowchart TB
A["Evaluate bonus = 0<br/>for one row"] --> B{"Is bonus NULL<br/>(unknown)?"}
B -- "yes" --> C["Result is UNKNOWN<br/>(not TRUE, not FALSE)"]
B -- "no" --> D{"Does bonus<br/>equal 0?"}
D -- "yes" --> E["Result is TRUE"]
D -- "no" --> F["Result is FALSE"]
C --> G{"Used in WHERE?"}
E --> G
F --> G
G -- "TRUE keeps the row" --> H["Row kept only<br/>on TRUE"]
G -- "UNKNOWN or FALSE" --> I["Row dropped<br/>-- use IS NULL instead"]
The one-line takeaway
NULL means "unknown," so it's never equal to anything (not even another NULL) — test it with IS NULL / IS NOT NULL, and use COALESCE(value, default) to replace missing values with a real one before you compare, compute, or display.
Where you'll meet it
You'll meet NULL the moment you touch real data: an OUTER JOIN manufactures NULLs for unmatched rows, optional form fields arrive empty, and "not yet" states (unshipped orders, unfinished tasks) show up as missing dates. It's one of the most common interview filters, usually disguised as a bug hunt: "why does WHERE status != 'shipped' drop the rows where status is NULL?" (because NULL != 'shipped' is UNKNOWN, not TRUE) or "why is this AVG higher than you expected?" (because it skipped the NULLs instead of treating them as 0). Reporting and dashboards lean on COALESCE constantly — COALESCE(nickname, full_name, 'Guest') to pick the best available label, or COALESCE(discount, 0) so a missing discount doesn't blank out a price. The engineers who stand out are the ones who say NULL out loud before it bites: they reach for IS NULL instead of = NULL, wrap columns in COALESCE before arithmetic, and can explain that the whole oddity flows from one idea — NULL means "I don't know."
Named concepts: NULL meaning (unknown), IS NULL / IS NOT NULL, three-valued logic, COALESCE