Three-Valued Logic

NULL means unknown. Not zero, not an empty string, not false. Unknown.

That has a consequence people meet exactly once and never forget: NULL is not equal to anything, including another NULL. WHERE year = NULL matches nothing, ever, and it does not error. It just quietly returns no rows.

IS NULL and IS NOT NULL are the only way to test for it. The = version returns nothing and does not complain.

A condition can be true, false, or unknown, and only true keeps a row. That is why a NULL fails both = 5 and <> 5 at the same time, which looks impossible until you remember there are three values rather than two.

Arithmetic spreads it too: 10 + NULL is NULL, because adding to an unknown gives an unknown.

✦ COUNT(*) and COUNT(column) are different

COUNT(*) counts rows. COUNT(copies) counts rows where copies is not null. The difference between them is exactly the number of missing values, which makes it a quick and useful way to measure the gaps in a table.