NULL and missing values

Game: chapters X and XI

Real data has holes. SQL marks a hole with NULL, and NULL follows rules of its own: most surprises in a query that "looks right" come from them.

What NULL means

NULL is not a value. It is the mark SQL puts in a cell when there is no value: the fact is unknown, or does not apply. It is not zero, which is a number, and it is not the empty string '', which is a piece of text with no letters in it. A price of 0 says "free"; a NULL price says "nobody wrote the price down".

In the bakery database (see the bakery database page), customer.referred_by holds the id of the customer who recommended the shop. Seven customers walked in on their own, so their referred_by is NULL. In the same way staff.manager_id holds the manager of each member of staff, and Margaret Hale, who owns the shop, answers to nobody: her manager_id is NULL. The columns are not wrong for them; there is simply nothing to put in them. Every rule on this page follows from recording an absence that way.

Three answers, not two: true, false and unknown

A comparison usually answers true or false. When one side is NULL, it answers unknown: is a missing manager equal to Tom Becker, staff member 2? Nobody can say. SQL's logic therefore has three values, and the rule that matters most is short: WHERE, HAVING and ON keep a row only when the condition is true. Unknown is thrown away, exactly like false.

The query below asks for the staff not managed by Tom Becker. Four of the six answer to him, so it looks as if it should return the other two, Tom himself and Margaret Hale, but Margaret is missing: for her manager_id <> 2 is unknown.

SELECT name, manager_id
FROM staff
WHERE manager_id <> 2
ORDER BY id;

If the owner belongs in the answer, say so: WHERE manager_id <> 2 OR manager_id IS NULL. A CASE makes the three outcomes visible. Its first branch catches true, its second catches false, and whatever is left over is unknown.

SELECT name,
       CASE
         WHEN manager_id = 2 THEN 'yes'
         WHEN NOT (manager_id = 2) THEN 'no'
         ELSE 'unknown'
       END AS managed_by_tom
FROM staff
ORDER BY id;

Notice that NOT does not rescue the owner: the opposite of unknown is still unknown. AND and OR follow common sense once you read unknown as "could be either":

False AND anything is false, whatever the unknown turns out to be; true OR anything is true. Every other mix stays unknown. Arithmetic behaves the same way: price + NULL is NULL, and so is 'Mr ' || NULL.

Asking about NULL: IS NULL and IS NOT NULL

Because a comparison with NULL is never true, referred_by = NULL keeps no rows at all, even on the rows where the column is NULL. SQL has a dedicated test instead: IS NULL, and its opposite IS NOT NULL. These two always answer true or false, never unknown, so they are safe anywhere a condition goes. Here they find the walk-ins who left an email (customer.email is NULL for those who gave none).

SELECT name, town
FROM customer
WHERE referred_by IS NULL
  AND email IS NOT NULL
ORDER BY id;

Aggregates skip NULL

COUNT(*) counts rows. COUNT(column) counts the rows where that column is not NULL. SUM, AVG, MIN and MAX also ignore NULLs: an average is taken over the known values only, not over all rows with the unknown ones treated as zero. One query shows the difference.

SELECT COUNT(*) AS customers,
       COUNT(referred_by) AS referred,
       COUNT(DISTINCT referred_by) AS referrers
FROM customer;

Forty customers, 33 of whom were referred, by 14 different people (Chloe Martin alone referred six). Each column answers a different question, and picking the wrong COUNT gives a plausible number that answers the wrong one.

An aggregate over no rows at all is a special case. COUNT of nothing is 0, but SUM, AVG, MIN and MAX of nothing are NULL: there is no total of an empty list. The Pistachio financier, product 19, was never ordered, so the sum of its quantities is NULL, not 0.

SELECT COUNT(*) AS lines, SUM(quantity) AS pieces
FROM order_item
WHERE product_id = 19;

Replacing NULL: COALESCE

COALESCE(a, b, ...) returns the first of its arguments that is not NULL. It is the standard way to show a default instead of a hole, and it works in every database (IFNULL is not standard SQL; see Grammar: COALESCE and IS). Its most common use is turning the NULL sum of an empty group back into a zero. Here each cake is joined to its order lines with a LEFT JOIN, so the Pistachio financier, which nobody bought, stays in the result with NULL quantities, and COALESCE reports 0 pieces.

SELECT p.name, COALESCE(SUM(oi.quantity), 0) AS pieces
FROM product AS p
LEFT JOIN order_item AS oi ON oi.product_id = p.id
WHERE p.category = 'cake'
GROUP BY p.id, p.name
ORDER BY p.id;

Use COALESCE for display, or where a NULL really means zero. Do not use it to hide NULLs that mean "unknown": replacing an unknown price by 0 before averaging pulls the average down with prices nobody ever charged. The reverse tool exists too: NULLIF(a, b) gives NULL when a equals b, which is how you avoid dividing by zero (total / NULLIF(orders, 0) is NULL instead of an error: PostgreSQL raises an error on division by zero, while SQLite already returns NULL).

Grammar: COALESCE and IS

The standard way to replace a NULL is COALESCE, as above; IFNULL is not standard SQL. A query with a red edge is one the game's terminal refuses: the answer under it is the terminal's own.

SELECT name, IFNULL(referred_by, 0) AS referrer
FROM customer;

IS goes only with NULL, TRUE or FALSE. To compare with a value, write =; when two NULLs must count as equal, write IS NOT DISTINCT FROM.

SELECT name
FROM customer
WHERE referred_by IS 1;

SELECT name
FROM customer
WHERE referred_by = 1
ORDER BY id;

NULL in joins

A join condition is a condition like any other, so a NULL key never matches anything, not even another NULL. Joining each member of staff to their manager with an inner join silently drops the owner: five rows come back, not six.

SELECT s.name, m.name AS manager
FROM staff AS s
JOIN staff AS m ON m.id = s.manager_id
ORDER BY s.id;

A LEFT JOIN keeps the unmatched rows and fills the right-hand columns with NULL, which brings a trap of its own when you count. With a left join, COUNT(*) counts the padded row too, so a customer with no order still counts one; COUNT(o.id) counts only real orders. Compare the two columns for Hugo Silva, Greta Rossi and Marco Costa, who never ordered.

SELECT c.name, COUNT(*) AS joined_rows, COUNT(o.id) AS orders
FROM customer AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE c.town = 'Hillcrest'
GROUP BY c.id, c.name
ORDER BY c.id;

The NOT IN trap

Which members of staff manage nobody? A natural first attempt asks for the staff whose id is not in the list of managers. It returns nothing at all.

SELECT name
FROM staff
WHERE id NOT IN (SELECT manager_id FROM staff)
ORDER BY id;

The list produced by the subquery contains NULL (the owner's empty manager_id). x NOT IN (1, 2, NULL) means x <> 1 AND x <> 2 AND x <> NULL, and the last part is unknown for every row, so no row is ever true. IN is not affected in the same way (a match is still a match), which makes the bug hard to see: the query is correct until one NULL appears in the data. Two fixes, both worth knowing: remove the NULLs from the list, or use NOT EXISTS (see Subqueries), which only asks whether a matching row exists and is never fooled by NULL.

SELECT name
FROM staff
WHERE id NOT IN (SELECT manager_id FROM staff WHERE manager_id IS NOT NULL)
ORDER BY id;

NULL in sorting and grouping

For GROUP BY and DISTINCT, all NULLs are treated as one value, so the seven walk-ins form a single group. For sorting, databases disagree: SQLite, which runs these examples, puts NULLs first in ascending order, while PostgreSQL puts them last. When it matters, do not rely on either: say so with NULLS FIRST or NULLS LAST. Here HAVING keeps the referrers who brought at least three customers, and the group of walk-ins comes last.

SELECT referred_by, COUNT(*) AS customers
FROM customer
GROUP BY referred_by
HAVING COUNT(*) >= 3
ORDER BY referred_by NULLS LAST;

Common mistakes

Writing = NULL or <> NULL instead of IS NULL or IS NOT NULL. Forgetting that <> and NOT also drop the NULL rows. Using COUNT(*) after a left join and counting the padded rows. Using NOT IN on a subquery whose column can be NULL. Expecting SUM of nothing to be 0. Replacing an unknown value with a made-up one through COALESCE before a calculation. Storing the text 'none' or 'N/A' instead of NULL, which makes IS NULL miss the rows and COUNT(column) count them.

Try it: Back again, Untidy emails and Money back, on the exercises page.

In the game: the archives record a missing value as NULL, like the bakery; Chapter X, Never Seen Together, and Chapter XI, The Silence, are where the rules of this page matter most.