NULL, and why your counts are wrong

NULL means unknown

It does not mean zero. It does not mean empty string. It means the database has no value here, and it will not pretend otherwise.

That produces three-valued logic. A comparison can be true, false, or unknown. And there is one rule that explains almost every NULL surprise you will ever hit:

WHERE keeps a row only when the condition is true. Not false. Not unknown.

The comparisons that never match

SELECT * FROM students WHERE phone = NULL;   -- always zero rows

Not an error. Zero rows, even for the students whose phone is NULL. “Unknown equals unknown” is unknown, not true. The correct form is a different operator entirely:

SELECT * FROM students WHERE phone IS NULL;
SELECT * FROM students WHERE phone IS NOT NULL;

The same rule bites on inequality, which is far less obvious. Suppose status is 'active', 'churned', or NULL for people who never set it.

SELECT COUNT(*) FROM users WHERE status <> 'churned';

The NULL people are not returned. NULL <> 'churned' is unknown, and unknown is not true, so those rows are dropped. You asked for everyone who is not churned and you got only the actives. Nothing warns you. The number is smaller than the truth, and it looks like a perfectly ordinary number.

WHERE (status <> 'churned' OR status IS NULL)   -- what you meant

The denominator you did not notice changing

Aggregates skip NULLs. This is usually what you want, and occasionally it destroys your analysis.

A survey has 1,000 responses. Two hundred people skipped the monthly income question.

• COUNT(*) → 1000
• COUNT(income) → 800
• AVG(income) → the total of 800 values, divided by 800

That last one is the trap. You will report “average monthly income of respondents: 47,200 pesos” and it is the average among the 800 who answered. If people with low incomes were likelier to skip the question (which is exactly what happens in real surveys) your number is biased upward, and nothing in the output hints at it.

Also: SUM over a column that is entirely NULL returns NULL, not 0. A revenue tile on a dashboard showing a blank rather than a zero has usually met this.

The NOT IN disaster

This one deserves its own warning.

SELECT * FROM students
WHERE id NOT IN (SELECT student_id FROM payments);

If even one row of payments.student_id is NULL, this returns zero rows, always. Because id NOT IN (1, 2, NULL) expands to id <> 1 AND id <> 2 AND id <> NULL, and that last term is unknown, so the whole AND can never be true.

Zero rows looks like a real answer. “Great, every student has paid.” Use NOT EXISTS or the anti-join from the joins lesson instead: both handle NULLs the way you expect:

SELECT s.* FROM students s
WHERE NOT EXISTS (SELECT 1 FROM payments p WHERE p.student_id = s.id);

Do not paper over it

COALESCE(income, 0) replaces NULL with zero. It is the right tool sometimes and a lie other times.

If your column means “amount refunded” and no refund happened, zero is correct. If your column means “reported income” and someone skipped the question, replacing it with zero manufactures 200 destitute respondents who do not exist. The distinction between “it was zero” and “we do not know” is real information, and once you have flattened it you cannot get it back.

The habit

For every nullable column in a query, decide out loud what should happen to the unknowns: counted, excluded, or replaced. Then write that decision into the query so the next person can see it. Most NULL bugs are not misunderstandings of SQL. They are decisions nobody made.