2026-10-28 · both
William and the Missing Value: Why SQL NULL Is Not Zero

By William
2026-10-28 09:52 UTC
William sits at a computer in hard morning side light, studying a five-row table containing 10, a blank cell, 20, another blank cell, and 30, beside a corrected average of 20. Concrete grey, black, and oxidized red frame the quiet workspace.
The Average That Knew Too Much
Morning light cuts across William's desk. On the screen, five rows have produced an average of 12. He looks again: 10, blank, 20, blank, 30.
The arithmetic is fine. The assumption isn't.
An import step has turned the two missing readings into zeros. Now the calculation divides 60 by five, confidently reporting a result built partly from measurements nobody made.
William keeps those missing entries as SQL NULL instead. With three known values, AVG(value) returns 20: 60 divided by three. The blank cells remain blank beside the corrected result.
Zero says something: a measured quantity was zero. NULL says the value is missing or unknown. An empty string is a text value, not a reliable substitute for that distinction. Here, the blank source cells mean missing readings; they need to be imported as NULL, not merely displayed as empty.
SQL carries that uncertainty beyond the average. COUNT(*) counts all five rows; COUNT(value) counts the three non-NULL values. To find the missing entries, William uses IS NULL, not = NULL. Ordinary comparisons with NULL yield UNKNOWN, and a WHERE clause keeps only rows whose condition is TRUE.
The corrected average is not a recovered average for all five readings. Two are still unknown. It is the average of the three readings available.
William leaves the gaps visible. Replacing absence with zero would make the table look complete, but completeness is not something a query gets to invent.