NULL and three-valued logic

NULL means unknown

Two customers in shop have no city: Elena and Hugo. Their city is NULL. That does not mean an empty string or “nowhere”. It means unknown: the city exists, we just do not know it.

That one idea explains all of NULL’s odd behavior. Is an unknown city equal to Pune? Unknown. Is it different from Pune? Also unknown.

SELECT count(*) FROM customers WHERE city = 'Pune';   -- 2

SELECT count(*) FROM customers WHERE city <> 'Pune';

SELECT name, city FROM customers WHERE city <> 'Pune';

    name    |   city
------------+----------
 Ben Okafor | Lagos
 Chen Wei   | Shanghai
 Farhan Ali | Karachi
 Grace Kim  | Seoul

Three-valued logic

SQL conditions have three possible results: true, false and unknown (written NULL). Any ordinary comparison with a NULL gives unknown:

SELECT NULL = NULL AS eq, NULL IS NULL AS is_null, 7 <> NULL AS ne;

 eq | is_null | ne
----+---------+----
    | t       |

Even NULL = NULL is unknown: two unknown values might or might not be equal. AND, OR and NOT follow from the same idea:

Asking about NULL correctly

Use IS NULL and IS NOT NULL. They always return true or false:

SELECT name, city FROM customers WHERE city IS NULL;

     name      | city
---------------+------
 Elena Petrova |
 Hugo Martin   |

When you want “different from Pune, counting unknown as different”, say so with IS DISTINCT FROM, which treats NULL like an ordinary value:

SELECT name, city FROM customers WHERE city IS DISTINCT FROM 'Pune';

That returns all six non-Pune customers, Elena and Hugo included.

To show a value in place of NULL, use COALESCE, which returns its first argument that is not NULL:

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

Check yourself

1. Why does WHERE city <> 'Pune' skip customers with no city?

   Show answer

   For them the comparison is with NULL, so the result is unknown, and WHERE keeps only rows where the condition is true.

   I could explain this without looking

2. What does NULL = NULL return, and how do you test whether a value is NULL?

   Show answer

   It returns NULL (unknown). Test with IS NULL or IS NOT NULL, which always return true or false.

   I could explain this without looking

3. Write a condition for “customers not in Pune, including those whose city is unknown”.

   Show answer

   city IS DISTINCT FROM 'Pune', or equivalently city <> 'Pune' OR city IS NULL.

   I could explain this without looking