SQL NULL: why two people vanish from both your queries

You have a table of six people, and two of them never told you their age. You run a query for everyone older than 30, and then a query for everyone not older than 30. You would expect the two answers to add up to six people. They add up to four. Two people vanished from both lists, and the database did not raise an error. This article explains where they went.

NULL means "no value", not zero

In a database, NULL is a marker that says "there is no value here". Maybe the person was never asked. Maybe they declined to answer. The database does not know which, and it does not guess. That makes NULL different from 0 (a real number) and different from '' (a real, empty piece of text). A person aged zero is a baby. A person whose age is NULL is a person whose age we do not know.

Because the value is unknown, a comparison with it has an unknown answer too. Is an unknown age greater than 30? We cannot say. SQL (the language most databases use for queries) therefore has three possible results for a condition, not two:

• TRUE: the condition holds.
• FALSE: the condition does not hold.
• UNKNOWN: at least one side is NULL, so nobody can tell.

The rule that matters most: a WHERE clause keeps a row only when the condition is TRUE. Both FALSE and UNKNOWN rows are dropped. That is how our two people disappeared.

Try it: pick a condition, watch each row

The table below is the same one from the opening. Caro and Eli have an unknown age. Pick a WHERE condition and see the result for every row. Rows with a green bar are the ones the query returns.

Start with age > 30: Ben and Fay come back. Now try NOT (age > 30). You get Ana and Dev, not Caro and Eli. NOT UNKNOWN is still UNKNOWN, because flipping "we cannot tell" does not make it known.

Next try age = NULL. It returns nothing at all, even though two rows clearly have a NULL age. The reason: NULL = NULL asks "is an unknown value equal to another unknown value?", and the honest answer is "unknown". Equality never works with NULL. SQL gives you a special test for it instead: age IS NULL, and its opposite age IS NOT NULL.

The same thing in real SQL

These queries were run against SQLite 3.45 with the same six rows. The table was created with CREATE TABLE people (name TEXT, age INTEGER).

SELECT name FROM people WHERE age > 30;           -- Ben, Fay
SELECT name FROM people WHERE NOT (age > 30);     -- Ana, Dev
SELECT name FROM people WHERE age = NULL;         -- (no rows)
SELECT name FROM people WHERE age IS NULL;        -- Caro, Eli
SELECT name FROM people
  WHERE age > 30 OR age IS NULL;                  -- Ben, Caro, Eli, Fay

The last line is the usual fix when you want "everyone who is not known to be 30 or younger": say out loud that unknowns count, with OR age IS NULL.

NULL spreads through arithmetic

Any arithmetic with NULL gives NULL. NULL + 1 is NULL, not 1. If you want a stand-in value, COALESCE(age, 0) returns the first argument that is not NULL. Be careful with this one: replacing an unknown age with 0 says "this person is a newborn", which is a claim you did not mean to make.

Counting and averaging skip NULLs

Aggregate functions (functions that squash many rows into one number) treat NULL in a special way. COUNT(*) counts rows. COUNT(age) counts only rows where age is not NULL. SUM and AVG ignore the NULL rows entirely. In the figure above, the four boxes under the table show this: COUNT(*) is 6, COUNT(age) is 4, SUM(age) is 131, and AVG(age) is 32.75, which is 131 divided by 4, not by 6. If you had wrongly counted the unknown ages as zero, the average would drop to about 21.8. Neither number is "wrong" as such, but you should know which one you are asking for.

What to do in your own code

Whenever a column can be NULL, ask yourself what an unknown value should do in each query. Use IS NULL and IS NOT NULL to test for it. Remember that WHERE keeps only TRUE rows, so a filter and its opposite may not cover every row. And if a column should never be empty, say so when you create the table with NOT NULL, so the database refuses to store an unknown in the first place.