NULLs and three-valued logic

The idea

Most languages have two answers: true and false. SQL has three: TRUE, FALSE, and UNKNOWN. Any comparison that touches a NULL returns UNKNOWN, because you genuinely cannot tell whether an unknown salary is above 50,000.

WHERE is strict: it keeps a row only when the predicate is exactly TRUE. UNKNOWN rows are discarded just as firmly as FALSE ones, which is why NULL rows vanish from a filter and from its negation.

Everything below follows from that single rule.

How it works

Three-valued logic is small enough to memorise. NOT UNKNOWN is UNKNOWN. UNKNOWN OR TRUE is TRUE (one witness is enough). UNKNOWN AND FALSE is FALSE. Everything else that touches UNKNOWN stays UNKNOWN. Then WHERE, ON and HAVING keep only TRUE.

-- 1. comparison with NULL is UNKNOWN — not true, not false
SELECT NULL = NULL;        -- UNKNOWN  (printed as NULL)
SELECT NULL <> 5;          -- UNKNOWN
SELECT NULL IS NULL;       -- TRUE     <- the only reliable test

-- 2. WHERE keeps TRUE only, so a NULL row misses both sides
SELECT * FROM scores WHERE score > 20;        -- NULL rows absent
SELECT * FROM scores WHERE NOT (score > 20);  -- NULL rows absent here too

-- 3. NOT IN with a NULL in the list can never be TRUE
--    x NOT IN (2, 3, NULL)
--      = NOT (x = 2 OR x = 3 OR x = NULL)
--      = NOT (FALSE OR FALSE OR UNKNOWN)  = NOT UNKNOWN = UNKNOWN
SELECT id FROM t
WHERE id NOT IN (SELECT parent_id FROM u);        -- empty if any parent_id IS NULL

SELECT id FROM t
WHERE NOT EXISTS (SELECT 1 FROM u WHERE u.parent_id = t.id);  -- NULL-safe

-- 4. aggregates skip NULLs; COUNT(*) does not
SELECT COUNT(*)     AS n_rows,   -- 5
       COUNT(score) AS n_known,  -- 4
       SUM(score)   AS total,    -- 105
       AVG(score)   AS mean      -- 26.25  = 105 / 4, NOT 105 / 5
FROM scores;

-- 5. COALESCE returns its first non-NULL argument, on the way out
SELECT AVG(COALESCE(score, 0)) FROM scores;   -- 21.00 = 105 / 5

Note the order in point 5: COALESCE replaces the unknown before the average is taken, so it changes the denominator as well as the sum. That is a modelling decision, “a missing score means zero”, not a fix for a bug.

Cost

The real cost of NULLs is rarely CPU: it is the silently empty result set that nobody notices for a week.

Watch out for

• = NULL matches nothing, and neither does <> NULL. Both evaluate to UNKNOWN for every row, so the filter returns zero rows without an error. Use IS NULL / IS NOT NULL (or IS DISTINCT FROM where supported).
• NOT IN over a nullable column returns an empty set. If the subquery can produce even one NULL, no row can ever be TRUE. Rewrite as NOT EXISTS, or add WHERE col IS NOT NULL inside the subquery, and remember plain IN is safe, because one TRUE match still wins.
• A filter and its negation do not cover the table. WHERE score > 20 plus WHERE score <= 20 will not add up to COUNT(*). In the same spirit, a CHECK constraint is satisfied unless it evaluates to FALSE, so a NULL slides straight past it.
• AVG is not SUM / COUNT(*). Aggregates ignore NULLs, so the denominator is COUNT(col). And over zero non-NULL values, SUM and AVG return NULL, not 0, while COUNT returns 0.
• NULL equality is inconsistent between operators. GROUP BY, DISTINCT and UNION treat two NULLs as the same thing, yet NULL = NULL is UNKNOWN, a standard UNIQUE index happily accepts many NULL rows, and ORDER BY parks NULLs first or last depending on the engine. Say what you mean with NULLS LAST.

Worked example

You have customers and orders, where orders.customer_id is nullable because a few walk-in orders were imported without an account. You write SELECT id FROM customers WHERE id NOT IN (SELECT customer_id FROM orders) to find customers who never ordered, and you get zero rows, even though you can plainly see customers with no orders.

Here is why. For customer 7, the engine expands the check into NOT (7 = 3 OR 7 = 9 OR … OR 7 = NULL). Every real comparison is FALSE, but the last term is UNKNOWN, and FALSE OR UNKNOWN is UNKNOWN. Negating it leaves UNKNOWN, so the gate drops customer 7, and by the same argument, everybody else. One imported walk-in emptied the whole report.

The fix is to ask the question in a form that never negates an unknown: WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id). Now each order either matches (TRUE) or does not (FALSE or UNKNOWN, both of which simply fail to produce a matching row), and the anti-join is usually faster too. If you then report the average order value, remember that AVG(amount) silently drops orders whose amount was never captured: decide deliberately between AVG(amount) and AVG(COALESCE(amount, 0)), and say which one the number is.

Check yourself