NULL Is Not a Value

In the shop, two customers have no city. The row exists. The city is missing: nobody entered it, or nobody knows it. SQL has a special marker for that, NULL. It is the most misunderstood idea in the language, because NULL is not a value. It is the absence of one, and the rules for it surprise almost everybody at first.

What NULL means

NULL means "unknown" or "missing". It is not zero, it is not an empty string and it is not the word "null". A shop with no recorded city for Dan Lim does not mean Dan lives in a city called "", it means we do not know.

psql shows NULL as a blank by default, which makes it look like an empty string. Turn on a visible marker so you can tell them apart. This setting only affects how psql prints. It does not touch the data.

\pset null '(null)'

Null display is "(null)".

SELECT name, city FROM customers ORDER BY name;

    name    |  city
------------+--------
 Ana Reyes  | Manila
 Ben Cruz   | Cebu
 Carla Diaz | Manila
 Dan Lim    | (null)
 Ella Tan   | Davao
 Finn Go    | Cebu
 Gina Ortiz | (null)
 Hugo Sy    | Manila
(8 rows)

Dan Lim and Gina Ortiz have no city. Everyone else has a text value, so only those two rows show the marker.

Comparing to NULL

Ask for customers with no city the way it looks natural, and you get nothing.

SELECT name FROM customers WHERE city = NULL;

 name
------
(0 rows)

No error, no rows, and no hint. The reason is that city = NULL asks "is the city equal to an unknown value?". The honest answer is "unknown", and WHERE only keeps rows where the answer is true. Unknown is not true, so the rows are dropped. Even NULL = NULL is unknown.

SELECT NULL = NULL AS equal, NULL IS NULL AS is_null;

 equal  | is_null
--------+---------
 (null) | t
(1 row)

The first is NULL, shown as (null) now, and the second is t for true. To test for NULL, use IS NULL and IS NOT NULL.

SELECT name FROM customers WHERE city IS NULL;

    name
------------
 Dan Lim
 Gina Ortiz
(2 rows)

NULL inside a condition

NULL rows also vanish from the opposite question. Ask for customers who are not in Manila.

SELECT name, city FROM customers WHERE city <> 'Manila' ORDER BY name;

   name   | city
----------+-------
 Ben Cruz | Cebu
 Ella Tan | Davao
 Finn Go  | Cebu
(3 rows)

Dan and Gina are not in Manila as far as anybody knows, yet they are missing. For them city <> 'Manila' is unknown, so they are dropped. Say what you mean. If missing cities should count as "not Manila", write it out, or use IS DISTINCT FROM, which treats NULL as an ordinary value.

SELECT name, city FROM customers WHERE city IS DISTINCT FROM 'Manila' ORDER BY name;

    name    |  city
------------+--------
 Ben Cruz   | Cebu
 Dan Lim    | (null)
 Ella Tan   | Davao
 Finn Go    | Cebu
 Gina Ortiz | (null)
(5 rows)

IS DISTINCT FROM is standard SQL and PostgreSQL supports it. Not every database does. The portable form is city <> 'Manila' OR city IS NULL.

NOT IN has a nastier version of the same trap. If the list contains a NULL, no row can ever be "not in" it.

SELECT name FROM customers WHERE city NOT IN ('Cebu', NULL);

 name
------
(0 rows)

Replacing NULL with a default

COALESCE returns the first argument that is not NULL. It is the usual way to show a friendly value.

SELECT name, COALESCE(city, 'unknown') AS city FROM customers ORDER BY name;

    name    |  city
------------+---------
 Ana Reyes  | Manila
 Ben Cruz   | Cebu
 Carla Diaz | Manila
 Dan Lim    | unknown
 Ella Tan   | Davao
 Finn Go    | Cebu
 Gina Ortiz | unknown
 Hugo Sy    | Manila
(8 rows)

The data did not change. Only the result did. NULL also spreads: any arithmetic or text join that touches a NULL gives NULL, so 'Manila' || NULL is NULL, not "Manila". Wrap the nullable part in COALESCE first.

NULLs sort together. In PostgreSQL they come last in an ascending sort and first in a descending one. You can say it directly with NULLS FIRST or NULLS LAST. Other databases pick a different default, so write it out when the order matters.

SELECT name, city FROM customers ORDER BY city NULLS FIRST, name LIMIT 4;

    name    |  city
------------+--------
 Dan Lim    | (null)
 Gina Ortiz | (null)
 Ben Cruz   | Cebu
 Finn Go    | Cebu
(4 rows)

Common mistakes

• Writing = NULL or <> NULL. Both are always unknown.
• Forgetting that <> and NOT IN drop the NULL rows.
• Storing "N/A", "none" or "" in a column instead of NULL. Then every query has to know the special word.
• Turning NULL into 0 and averaging the result, which changes the answer. Section 4 shows why.

If you skip this, your queries will return plausible answers that quietly leave out the rows nobody filled in. Which column in your own data has missing values that a query of yours has never counted?