NULL: the missing value

NULL means "no value" or "unknown". Any comparison with it, even NULL = NULL, is unknown rather than true. So you test for it with IS NULL, and replace it with COALESCE.

Why it matters

Real data has gaps: an employee with no manager, an order with no amount. NULL causes more wrong answers than anything else in SQL, because it doesn't behave like a normal value. Rows quietly vanish from filters, and sums and averages change, without any error message.

The idea

NULL is not zero and not empty text; it means "we don't know". So SQL uses three-valued logic: a condition can be true, false or unknown. manager_id = NULL is unknown for every row — even a NULL one, because "is this unknown thing equal to that unknown thing?" is itself unknown. WHERE keeps only true, so the query returns nothing.

To test for NULL, use IS NULL or IS NOT NULL. Any arithmetic with NULL gives NULL (10 + NULL is NULL). COALESCE(a, b, …) returns the first value that isn't NULL, which is how you put in a default.

A form with a blank box for "age". Is a blank age over 30? You can't say yes, and you can't say no — you just don't know. Are two blank ages equal? You don't know that either.

A person looking at two blank boxes might say "they're both blank, so they match". SQL never does that with =. Only IS NULL, IS NOT DISTINCT FROM, and grouping (GROUP BY puts all NULLs together) treat NULLs as alike.

Syntax

WHERE column IS NULL
WHERE column IS NOT NULL
COALESCE(column, default_value)

How it works

  1. WHERE manager_id = NULL: for Asha, NULL = NULL is unknown; for everyone else, 1 = NULL is unknown. Nothing is true, so no rows come back.
  2. WHERE manager_id IS NULL: true for Asha only. IS NULL is a real test that answers yes or no.
  3. WHERE dept_id <> 1: for Gus, NULL <> 1 is unknown, so Gus is dropped — even though he is clearly not in department 1.
  4. To keep him, say so: WHERE dept_id <> 1 OR dept_id IS NULL.
  5. COALESCE(amount, 0) turns order 106's NULL into 0 and leaves the other amounts alone.

Key points

Common mistakes

Not true: Replacing NULL with 0 leaves an average as it was.
In fact: AVG skips NULLs, but it counts zeros. Turning a missing value into 0 pulls the average down, so first decide what the gap really means.

In the interview

NULL represents a missing or unknown value, and SQL uses three-valued logic: comparisons with NULL return unknown, and WHERE keeps only rows that are true. So I never compare with equals NULL — I use IS NULL or IS NOT NULL — and I watch for filters like not-equal or NOT IN silently dropping NULL rows. When I need a default I use COALESCE, but only after deciding whether the gap really means zero.

Part of Unit 1: Reading data: SELECT, WHERE and sorting in the free SQL course.