Missing values and three-valued logic

The idea

NULL represents missing or unknown data, not an ordinary value equal to zero or empty text. Most comparisons involving NULL produce an unknown result. WHERE keeps true results, so both false and unknown conditions exclude a row. Use IS NULL or IS NOT NULL when the question is specifically whether a value is missing.

COALESCE chooses the first non-NULL argument and can make a deliberate display or calculation policy explicit. It should not conceal the difference between an unknown quantity and a known zero when that distinction matters. Aggregation also distinguishes them: COUNT(*) counts rows, while COUNT(column) counts non-NULL values. Build examples with a real zero, a missing value, and an ordinary value before deciding how a report should treat each.

WITH samples(value) AS (VALUES (0),(NULL),(5))
SELECT COUNT(*) AS rows_seen, COUNT(value) AS known, SUM(COALESCE(value,0)) AS total
FROM samples;

rows_seen | known | total
3         | 2     | 5

All three rows exist, but only two contain known values. The explicit zero default makes the sum’s chosen missing-value policy visible.