NULL, and the other traps

Three answers, not two

Most programming languages have two answers to a question: true or false. SQL has three: true, false and unknown. NULL means "a value we don't know", and any question about an unknown value has an unknown answer.

SELECT 1 = 1 AS a, 1 = 2 AS b, 1 = NULL AS c, NULL = NULL AS d, NULL <> NULL AS e;

 a | b | c | d | e 
---+---+---+---+---
 t | f |   |   | 
(1 row)

t is true and f is false. The empty ones are unknown, which psql shows as NULL. Is 1 equal to an unknown number? Unknown. Is one unknown equal to another? Also unknown. Two people whose ages you don't know might be the same age, or might not.

Think of a form with a box left blank. "Is this person over 18?" If the age box is blank, the honest answer isn't yes and isn't no; it's "can't tell". SQL is being honest in the same way, every time.

The only questions about NULL with a true or false answer are the ones built for it:

SELECT (NULL = NULL) IS NULL AS unknown, NULL IS NULL AS is_null,
       NULL IS DISTINCT FROM NULL AS distinct_nulls;

 unknown | is_null | distinct_nulls 
---------+---------+----------------
 t       | t       | f
(1 row)

IS NULL asks "is this unknown?", and gets a real answer. IS DISTINCT FROM, from Lesson 7, treats two NULLs as the same value, so they're not distinct.

How unknown spreads

AND, OR and NOT follow common sense, if you read unknown as "could be either":

SELECT true AND NULL AS t_and_n, false AND NULL AS f_and_n,
       true OR NULL AS t_or_n, false OR NULL AS f_or_n, NOT NULL AS not_n;

 t_and_n | f_and_n | t_or_n | f_or_n | not_n 
---------+---------+--------+--------+-------
         | f       | t      |        | 
(1 row)

• false AND unknown is false. Whatever the unknown is, both can't be true.
• true OR unknown is true. Whatever the unknown is, one of them already is.
• Everything else stays unknown, including NOT unknown. The opposite of "can't tell" is still "can't tell".

And WHERE keeps a row only when its condition is true. Unknown goes the same way as false. That's every NULL surprise so far, in one rule. Watch what it does to the county. Towns over 10,000 people:

SELECT count(*) FROM towns WHERE population > 10000;

 count 
-------
     5
(1 row)

Towns that aren't:

SELECT count(*) FROM towns WHERE NOT (population > 10000);

 count 
-------
     6
(1 row)

Five and six. There are twelve towns. Ask for both at once, a condition that sounds like it must cover everything:

SELECT count(*) FROM towns WHERE population > 10000 OR NOT (population > 10000);

 count 
-------
    11
(1 row)

Eleven. Dunmore, with no population, is neither over 10,000 nor not over 10,000. For Dunmore, both halves are unknown, and so is the whole thing. Splitting rows into "matches" and "doesn't match" never adds up when there are NULLs. There's always a third group.

NOT IN, and the query that returns nothing

The villages' phone numbers:

SELECT phone FROM towns WHERE kind = 'village';

     phone      
----------------
 (555) 010-2200
 (555) 010-6600
 
(3 rows)

Two numbers, and Juniper's NULL. Now, towns whose phone isn't one of the villages':

SELECT name FROM towns WHERE phone NOT IN (SELECT phone FROM towns WHERE kind = 'village');

 name 
------
(0 rows)

No rows at all, not even Ashford, whose phone is nothing like the villages'. Here's why. NOT IN (a, b, NULL) means "not equal to a, and not equal to b, and not equal to NULL". For Ashford, the first two are true, and the last is unknown. true AND true AND unknown is unknown, and WHERE drops it. The same happens to every row. One NULL in the list, and NOT IN can never be true. No error, no warning: just an empty answer that looks like "there aren't any".

Two fixes. Keep the NULLs out of the list:

SELECT name FROM towns
WHERE phone NOT IN (SELECT phone FROM towns WHERE kind = 'village' AND phone IS NOT NULL);

     name      
---------------
 Ashford
 Brennan Falls
 Cedar Hollow
 Dunmore
 Fairview
 Glen Oak
 Harlow
 Kestrel Bay
 Linden
(9 rows)

Or use NOT EXISTS, from Lesson 5, which asks "is there a matching row?", and only ever answers yes or no:

SELECT t.name FROM towns t
WHERE NOT EXISTS (SELECT 1 FROM towns v WHERE v.kind = 'village' AND v.phone = t.phone);

     name      
---------------
 Ashford
 Brennan Falls
 Cedar Hollow
 Dunmore
 Fairview
 Glen Oak
 Harlow
 Juniper
 Kestrel Bay
 Linden
(10 rows)

Ten, not nine: Juniper is in this one. Its own phone is NULL. With NOT IN, "is NULL not in the list?" is unknown, so Juniper was dropped. With NOT EXISTS, "is there a village with Juniper's phone?" is plainly no, since NULL matches nothing, so Juniper is kept. Neither is wrong, but they answer slightly different questions, and only one of them says what it means. Prefer NOT EXISTS to NOT IN with a subquery. It never returns nothing by accident.

coalesce: filling in a NULL

coalesce returns the first of its arguments that isn't NULL. Its everyday use is display:

SELECT name, coalesce(website, '(none)') AS website FROM towns
WHERE website IS NULL OR id = 1;

  name   |           website           
---------+-----------------------------
 Ashford | https://www.ashfordcity.gov
 Ashford | (none)
 Juniper | (none)
(3 rows)

Using it in a calculation is a decision, not a fix:

SELECT avg(population) AS avg_skip, avg(coalesce(population, 0)) AS avg_as_zero FROM towns;

      avg_skip      |    avg_as_zero     
--------------------+--------------------
 15659.363636363636 | 14354.416666666667
(1 row)

avg skips Dunmore. coalesce(population, 0) tells it Dunmore has nobody, which is false, and drags the average down by 1,300. Replace NULL with zero only when zero is the truth.

Here's a case where it is. Juniper's total meeting attendance:

SELECT sum(attendance) AS total, count(attendance) AS counted,
       coalesce(sum(attendance), 0) AS total_or_zero
FROM meetings WHERE town_id = 10;

 total | counted | total_or_zero 
-------+---------+---------------
       |       0 |             0
(1 row)

Juniper held no meetings. count of nothing is 0, but sum of nothing is NULL, not 0. For a report of "total attendance", zero is right: no meetings, nobody attended. That's when coalesce(sum(...), 0) belongs.

NULL in text

Join text with ||, and one NULL makes the whole thing NULL:

SELECT name, name || ' - ' || website AS joined, concat(name, ' - ', website) AS with_concat
FROM towns WHERE id IN (1, 2);

  name   |                joined                 |              with_concat              
---------+---------------------------------------+---------------------------------------
 Ashford | Ashford - https://www.ashfordcity.gov | Ashford - https://www.ashfordcity.gov
 Ashford |                                       | Ashford - 
(2 rows)

The village's line disappears entirely, name and all. concat treats NULL as empty text instead, so the name survives, with a dangling dash. concat_ws ("with separator") goes one better, and skips NULLs, separator and all:

SELECT name, concat_ws(', ', website, phone) AS contact FROM towns WHERE id IN (1, 2, 10);

  name   |                   contact                   
---------+---------------------------------------------
 Ashford | https://www.ashfordcity.gov, (555) 010-1100
 Ashford | (555) 010-2200
 Juniper | 
(3 rows)

No stray commas. Juniper has neither, so it gets empty text.

nullif, and dividing by zero

How many meetings per published set of minutes? The Ashford village and Cedar Hollow have published none:

SELECT town_id, count(minutes_url) AS with_minutes FROM meetings
WHERE town_id IN (2, 4) GROUP BY town_id;

 town_id | with_minutes 
---------+--------------
       2 |            0
       4 |            0
(2 rows)

Divide by that:

SELECT town_id, count(*) / count(minutes_url) AS per_minutes FROM meetings
WHERE town_id IN (2, 4) GROUP BY town_id;

ERROR:  division by zero

The whole query fails, for every row, because of one zero. In a report over 3,000 cities, one city with no minutes would take the whole report down. nullif(a, b) is the opposite of coalesce: it returns NULL when a equals b, and a otherwise. Turn the zero into NULL, and dividing by NULL gives NULL, not an error:

SELECT town_id, count(*) / nullif(count(minutes_url), 0) AS per_minutes FROM meetings
WHERE town_id IN (2, 4) GROUP BY town_id;

 town_id | per_minutes 
---------+-------------
       2 |            
       4 |            
(2 rows)

"Meetings per set of minutes" has no answer for a town with no minutes, and NULL says exactly that. x / nullif(y, 0) is the standard guard on any division.

Empty is not NULL

Empty text, '', is a value: text with nothing in it. NULL is no value. They look identical in psql's output, and they behave differently. Give Juniper an empty website, inside a transaction to undo afterwards:

BEGIN;
UPDATE towns SET website = '' WHERE id = 10;
SELECT id, name, website, website IS NULL AS is_null, length(website) AS len
FROM towns WHERE id IN (2, 10);

 id |  name   | website | is_null | len 
----+---------+---------+---------+-----
  2 | Ashford |         | t       |    
 10 | Juniper |         | f       |   0
(2 rows)

Both websites look blank. The Ashford village's is NULL, with no length. Juniper's is empty text, with a length of 0. And every check that looks for NULL now misses Juniper:

SELECT count(website) AS has_website FROM towns;
SELECT name FROM towns WHERE website IS NULL;

 has_website 
-------------
          11
(1 row)

  name   
---------
 Ashford
(1 row)

Eleven towns "have a website", one of them an empty one, and only one town is missing one. Empty strings creep in from forms, spreadsheets and imports, wherever a blank box is saved as '' instead of nothing. Treat them the same, with nullif: trim the spaces, and turn empty into NULL.

SELECT name FROM towns WHERE nullif(btrim(website), '') IS NULL;

  name   
---------
 Ashford
 Juniper
(2 rows)

Both found. Better still, stop it at the door with Lesson 6's rule: CHECK (website <> ''), so empty can't be stored, and "no website" has only one way to be written. Undo the change:

ROLLBACK;

DISTINCT and NULL

DISTINCT and GROUP BY treat all NULLs as the same value. count(DISTINCT ...), like every aggregate, skips them:

SELECT count(DISTINCT website) AS distinct_sites, count(*) AS towns FROM towns;

 distinct_sites | towns 
----------------+-------
             10 |    12
(1 row)

Ten different websites among twelve towns, and the two with none aren't counted as an eleventh. It's the right answer, as long as you know that's the question it answered.

Traps that look like values

Not every trap is NULL. These three look like perfectly ordinary data.

Numbers stored as text sort as text. Text sorts one character at a time, so '100' comes before '25', because 1 comes before 2:

SELECT x FROM (VALUES ('9'), ('10'), ('100'), ('25')) AS v(x) ORDER BY x;

  x  
-----
 10
 100
 25
 9
(4 rows)

(VALUES makes a small table on the spot, handy for trying things.) Convert to a number with ::integer, a cast, and it sorts properly:

SELECT x FROM (VALUES ('9'), ('10'), ('100'), ('25')) AS v(x) ORDER BY x::integer;

  x  
-----
 9
 10
 25
 100
(4 rows)

Better: store numbers in a number column in the first place, the Lesson 6 way.

Floating point isn't exact. float8 is the type most languages use for decimals, and it stores numbers in binary, where 0.1 can't be written exactly, any more than one third can in decimal:

SELECT 0.1::float8 + 0.2::float8 AS float_sum,
       0.1::float8 + 0.2::float8 = 0.3::float8 AS float_equal,
       0.1 + 0.2 = 0.3 AS numeric_equal;

      float_sum      | float_equal | numeric_equal 
---------------------+-------------+---------------
 0.30000000000000004 | f           | t
(1 row)

In floating point, 0.1 + 0.2 isn't 0.3, so an equality check fails. Without the casts, Postgres reads 0.1 as numeric, which is exact. That's why Lesson 6 said numeric for money, always.

Dates written as text sort as text. American-style dates, month first:

SELECT d FROM (VALUES ('3/1/2026'), ('12/15/2025'), ('2/14/2026')) AS v(d) ORDER BY d;

     d      
------------
 12/15/2025
 2/14/2026
 3/1/2026
(3 rows)

That looks right, and it's luck: text compares character by character, and 1 happens to come before 2, which comes before 3. Add 9/1/2025, and it would sort last, after the 2026 dates, because 9 comes after 3. Written year first, text sorts the same way the dates do, every time:

SELECT d FROM (VALUES ('2026-03-01'), ('2025-12-15'), ('2026-02-14')) AS v(d) ORDER BY d;

     d      
------------
 2025-12-15
 2026-02-14
 2026-03-01
(3 rows)

That's why Lesson 3 said to write dates year first. Better still, store them in a date column, where the question doesn't arise.

And integer division, one last time, with %, the remainder:

SELECT 7 / 2 AS int_div, 7 / 2.0 AS num_div, 7 % 2 AS remainder;

 int_div |      num_div       | remainder 
---------+--------------------+-----------
       3 | 3.5000000000000000 |         1
(1 row)

Spot the problem

Work out what's wrong before you read the explanation.

The all-clear. A data check is meant to find leaders whose town doesn't exist: SELECT * FROM leaders WHERE town_id NOT IN (SELECT id FROM towns); It's run after every import, returns no rows every time, and everyone relaxes. Then an import brings in a leader whose town couldn't be matched, so the script left town_id empty. The check still says no rows. Why? The town_id column has no NOT NULL, so the empty value was accepted (a foreign key allows NULL too). For that row, "is NULL not in the list of towns?" is unknown, so WHERE drops it, and it never appears. The check passed by failing to ask. A NOT EXISTS would have kept it, and a NOT NULL on town_id would have stopped it getting in at all.

Where you are now

You know why NULL behaves the way it does, how to stop it hiding rows, and three more traps that look like ordinary values. Lesson 9 is about speed: indexes, and reading EXPLAIN to see how the database actually finds your rows.

Check yourself

Key terms

• Three-valued logic: SQL's true, false and unknown. WHERE keeps only true.
• NULL: no value, meaning unknown. Comparisons with it are unknown.
• coalesce: the first argument that isn't NULL.
• nullif: NULL if the two arguments are equal, otherwise the first.
• Empty string: '', text with nothing in it. A value, not NULL.
• Cast: converting a value to another type, like x::integer.
• Floating point: an inexact binary number type, float8. Use numeric when exact matters.

Go deeper

• The PostgreSQL manual: Comparison Functions and Operators (opens in a new tab), and Conditional Expressions (opens in a new tab) for coalesce and nullif.
• Try: towns with no meeting that has minutes, with NOT IN and then NOT EXISTS. Do they agree? Why?
• Try: each town's average meeting attendance, showing (no meetings) for Juniper instead of a blank.