NULL, and why it is neither zero nor empty

NULL means unknown or not applicable. Not zero, not an empty string, not false. Getting that distinction wrong produces queries that return the wrong rows and never error, which is the worst kind of bug.

This is the lesson that catches everybody. It is worth the twenty-five minutes.

`= NULL` is never true

SELECT count(*) AS eq_null FROM members WHERE email = NULL;

 eq_null
---------
       0

SELECT count(*) AS is_null FROM members WHERE email IS NULL;

 is_null
---------
       2

Two members have no email. The first query found none of them, because it asked "is this unknown value equal to NULL?" — and the answer to that is not yes and not no. It is unknown, which is not true, so the row is not returned.

Use IS NULL and IS NOT NULL. There is no other way.

Three-valued logic

SQL has three truth values: TRUE, FALSE and UNKNOWN. Any comparison involving NULL produces UNKNOWN.

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

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

The first two print as blank — that is psql showing NULL, which here is the UNKNOWN result. Only IS NULL returns an actual boolean.

The truth tables, which are worth reading once:

AND    | TRUE     FALSE    UNKNOWN          OR     | TRUE  FALSE    UNKNOWN
-------+---------------------------          -------+-------------------------
TRUE   | TRUE     FALSE    UNKNOWN          TRUE   | TRUE  TRUE     TRUE
FALSE  | FALSE    FALSE    FALSE            FALSE  | TRUE  FALSE    UNKNOWN
UNKNOWN| UNKNOWN  FALSE    UNKNOWN          UNKNOWN| TRUE  UNKNOWN  UNKNOWN

Two entries are worth noticing: FALSE AND UNKNOWN is FALSE (if one side is definitely false, the whole thing is false regardless), and TRUE OR UNKNOWN is TRUE for the same reason. Everything else involving UNKNOWN stays unknown.

WHERE keeps only rows where the condition is TRUE. FALSE and UNKNOWN are both discarded, which is why NULL rows quietly vanish.

`NULL` poisons arithmetic and concatenation

SELECT 100 + NULL AS sum, 'abc' || NULL AS concat;

 sum | concat
-----+--------
     |

Both are NULL. Unknown plus a hundred is still unknown.

This matters in real queries:

SELECT title, price_paise + 5000 AS with_postage FROM books;

Any book with a NULL price gets a NULL total rather than 5000. That is correct — the total genuinely is unknown — and it is frequently not what the report needed.

The aggregate that counts differently

SELECT count(*) AS all_loans, count(returned_on) AS returned FROM loans;

 all_loans | returned
-----------+----------
       180 |      136

count(*) counts rows. count(column) counts non-NULL values in that column. The difference here is 44, which is exactly the number of loans still out.

That is a genuinely useful idiom — count(returned_on) is "how many have been returned" without a WHERE — and it is also a trap when you meant to count rows and typed a column name.

Every other aggregate ignores NULL too:

SELECT avg(price_paise) FROM books;

That is the average of the books that have a price, not of all books. If ten of forty books had no price, the average is over thirty. Whether that is right depends entirely on the question, and nothing tells you which happened.

The `NOT IN` trap, which is the worst one

SELECT count(*) AS books_never_out FROM books
WHERE id NOT IN (SELECT book_id FROM loans WHERE returned_on IS NULL);

 books_never_out
-----------------
              14

Correct. Now watch what one NULL in that list does:

SELECT count(*) AS with_a_null_in_the_list FROM books
WHERE id NOT IN (SELECT book_id FROM loans WHERE returned_on IS NULL
                 UNION ALL SELECT NULL);

 with_a_null_in_the_list
-------------------------
                       0

Fourteen becomes zero. No error, no warning.

The reason: x NOT IN (a, b, NULL) expands to x <> a AND x <> b AND x <> NULL. That last comparison is UNKNOWN, and TRUE AND UNKNOWN is UNKNOWN — so the whole condition can never be TRUE, for any row.

IN does not have this problem in the same way, because TRUE OR UNKNOWN is TRUE.

The fix is NOT EXISTS:

SELECT count(*) AS using_not_exists FROM books b
WHERE NOT EXISTS (
  SELECT 1 FROM loans l WHERE l.book_id = b.id AND l.returned_on IS NULL
);

 using_not_exists
------------------
               14

Prefer NOT EXISTS to NOT IN with a subquery, always. It is immune to this, and it is usually faster as well. If you must use NOT IN, add WHERE col IS NOT NULL to the subquery — and you will forget, one day, on a column that only started containing NULL last month.

`COALESCE` and `NULLIF`

SELECT name, COALESCE(email, 'no email on file') AS contact
FROM members WHERE email IS NULL;

    name     |     contact
-------------+------------------
 Meera Naik  | no email on file
 Neha Phadke | no email on file

COALESCE returns its first non-NULL argument, and takes as many as you like:

COALESCE(mobile, landline, 'no phone')

Use it for display, and be careful using it in a WHERE — COALESCE(price_paise, 0) > 100 wraps the column in a function, which as the filtering lesson noted can stop an index being used.

NULLIF is the reverse — it turns a specific value into NULL:

SELECT NULLIF(5, 5) AS same, NULLIF(5, 3) AS different;

 same | different
------+-----------
      |         5

Its one common use is avoiding division by zero: total / NULLIF(count, 0) gives NULL rather than an error.

When to allow `NULL` at all

A design question, and the default should be no.

Use NOT NULL unless you can say what NULL means for that column. If you cannot answer "what does it mean for this to be unknown?", it should be required.

Good uses of NULL:

returned_on — genuinely not applicable until the book comes back. This is the ideal case: NULL means "has not happened".
date_of_birth — the member did not tell us.
died on an author — they are alive.

Bad uses:

copies as NULL meaning zero. Use 0; zero is a known quantity.
name as NULL meaning empty. Use '' or require it.
A status column where NULL means "pending". Use the word 'pending'.

NULL meaning something specific is the mistake. The moment NULL means "pending" you have a value that cannot be compared, cannot be grouped normally, and silently disappears from filters.

And a warning for module 4: a UNIQUE constraint permits many NULLs, because two unknowns are not known to be equal. Three members with no email do not violate UNIQUE (email).

Check your work

What NULL means. Unknown or not applicable.

Why = NULL returns nothing. The comparison is UNKNOWN, and WHERE keeps only TRUE.

The three truth values. TRUE, FALSE, UNKNOWN.

The two truth-table entries worth remembering. FALSE AND UNKNOWN is FALSE; TRUE OR UNKNOWN is TRUE.

What NULL does to arithmetic and ||. Makes the whole result NULL.

count(*) versus count(column). Rows, versus non-NULL values — 180 against 136 here.

What avg() averages. Only the non-NULL values, and nothing tells you how many were skipped.

Why NOT IN with a NULL returns nothing. It becomes ... AND x <> NULL, which is UNKNOWN, so the condition is never TRUE.

What to use instead. NOT EXISTS, always.

What COALESCE does, and where to be careful. First non-NULL argument; wrapping a column in it can prevent index use.

The one common use of NULLIF. Avoiding division by zero.

When to allow NULL. Only when you can say what unknown means for that column. returned_on is the ideal case.

What UNIQUE does about NULL. Permits many of them.

Practice

Count members with no email using = NULL, then IS NULL.
Evaluate NULL = NULL, NULL <> NULL and NULL IS NULL.
Work out FALSE AND NULL and TRUE OR NULL on paper, then check in psql.
Compute 100 + NULL and 'abc' || NULL.
Compare count(*) and count(returned_on) on loans. Explain the difference of 44.
Compute avg(fine_paise) and then count(fine_paise). Explain what the average is over.
Run the NOT IN example both ways and confirm 14 becomes 0.
Explain, in writing, why it becomes 0.
Rewrite it with NOT EXISTS and confirm 14.
Use COALESCE to show 'no phone' for members without one.
Use NULLIF to make total / NULLIF(n, 0) safe. Then remove it and cause the error.
Insert three members with NULL emails into a table with UNIQUE (email). Note that all three are accepted.
Go through the six tables and list every nullable column. For each, write what NULL means there. Any you cannot answer should be NOT NULL.

Official documentation

PostgreSQL — Comparison functions and operators — IS NULL, IS DISTINCT FROM and the full three-valued behaviour.
PostgreSQL — Conditional expressions — COALESCE, NULLIF, GREATEST and LEAST.
PostgreSQL — Aggregate functions — Including the note that every aggregate except count(*) ignores NULL.
PostgreSQL — Subquery expressions — IN, NOT IN and EXISTS, with the NULL behaviour spelled out.
PostgreSQL — Unique constraints — Why many NULLs are allowed.

Next: pattern matching and ranges.

= NULL is never true

Three-valued logic

NULL poisons arithmetic and concatenation

The aggregate that counts differently

The NOT IN trap, which is the worst one

COALESCE and NULLIF

When to allow NULL at all