NULL and the unknown

Why NULL isn't zero, and how to test for it.

NULL means "unknown"

Some published years are missing — stored as NULL. NULL is not zero and not an empty string; it means unknown. That has a surprising consequence: any comparison with NULL yields NULL (treated as not-true), so published = NULL matches nothing.

To test for it you must use IS NULL / IS NOT NULL:

SELECT title FROM books WHERE published IS NULL;

COALESCE(a, b) returns the first non-NULL argument — handy for a default: COALESCE(published, 0).

Only a true condition keeps a row. Both comparisons come back unknown, so the one book with no published year falls out of the equality test AND out of its negation — which is exactly why IS NULL has to exist.