IS NULL vs = NULL — why = NULL returns zero rows, every time

On SQL Quest's employees table, WHERE manager_id IS NULL returns 7 rows — the people with no manager. WHERE manager_id = NULL returns 0 rows. Same table, same instant. Not an error, not a warning — just silently nothing. Here is the one idea that explains it, and the two places it will bite you again.

NULL is a question mark, not a value

NULL means unknown. So manager_id = NULL asks "does this value equal an unknown?" — and SQL's honest answer is unknown. WHERE keeps only rows whose condition is true; unknown is not true; every row drops. The kicker: even NULL = NULL is unknown — two mystery boxes are not "equal" just because both are mysteries.

-- 7 rows: the executives with no manager
SELECT name FROM employees WHERE manager_id IS NULL;

-- 0 rows, always, on every engine
SELECT name FROM employees WHERE manager_id = NULL;

-- and the complement: everyone WITH a manager (43 rows)
SELECT name FROM employees WHERE manager_id IS NOT NULL;

Where it bites next: NOT IN

The equality rule hides inside other operators. The famous one:

-- looks right, returns NOTHING if any manager_id in the list is NULL
SELECT name FROM employees
WHERE emp_id NOT IN (SELECT manager_id FROM employees);

Why: NOT IN (1, 2, NULL) expands to x != 1 AND x != 2 AND x != NULL — and that last comparison is unknown, which poisons the whole AND chain to unknown. Zero rows, no error, and a very confused analyst. The fixes: filter the NULLs (WHERE manager_id IS NOT NULL inside the subquery) or use NOT EXISTS, which handles NULLs correctly by construction.

The repair kit: COALESCE and friends

-- COALESCE: first non-NULL — the default-value tool
SELECT name, COALESCE(manager_id, 0) AS mgr FROM employees;

-- NULL-safe bucketing in reports
SELECT COALESCE(CAST(manager_id AS TEXT), '(no manager)') AS mgr, COUNT(*)
FROM employees GROUP BY mgr;

-- NULLIF: the inverse — turn a sentinel INTO NULL (kills div-by-zero)
SELECT total / NULLIF(quantity, 0) FROM orders;

Two more rules that follow from "NULL is unknown": arithmetic propagates it (5 + NULL → NULL — one NULL column silently nulls a whole computed metric), and aggregates skip it (COUNT(manager_id) counts 43, COUNT(*) counts 50 — the gap is your NULL count).

▶ Practice: Handling NULL Values

FAQ

Why doesn't SQL just error on = NULL?

Because it is a legal comparison with a legal result — unknown. The standard chose three-valued logic over runtime errors. Linters and code review are your guard rail; the engine will not be.

Is there an equality that treats two NULLs as equal?

Yes: IS NOT DISTINCT FROM (Postgres, standard), <=> (MySQL), IS (SQLite). Useful when comparing nullable columns in joins or dedup logic.

Does NULL equal empty string?

No — '' is a known value (a string of length zero); NULL is the absence of one. Oracle is the notorious exception that historically conflates them; everywhere else the distinction is real and load-bearing.