NULL & three-valued logic

UNKNOWN vs FALSE; IS [NOT] NULL vs =; COALESCE/NULLIF; NULL propagation; aggregates ignore NULLs.

• ★NULL = NULL is not TRUE

  NULL = NULL is UNKNOWN, not TRUE; use IS [NOT] NULL, or IS DISTINCT FROM as the null-safe comparison.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE a(k INT); INSERT INTO a VALUES (1),(NULL); CREATE TABLE b(k INT); INSERT INTO b VALUES (1),(NULL);

  The trap

  SELECT a.k FROM a JOIN b ON a.k = b.k ORDER BY a.k;

  1

  The fix

  SELECT a.k FROM a JOIN b ON a.k IS NOT DISTINCT FROM b.k ORDER BY a.k NULLS LAST;

  1

  NULL

  The vendor with a NULL code never matches its twin — NULL = NULL is UNKNOWN, so the join drops it; IS NOT DISTINCT FROM treats two NULLs as equal.

• WHERE keeps only TRUE

  WHERE drops UNKNOWN and FALSE alike — the root cause behind half the P01/P04 traps.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE tickets(id INT, priority VARCHAR); INSERT INTO tickets VALUES (1,'high'),(2,NULL),(3,'low');

  The trap

  SELECT id FROM tickets WHERE priority <> 'high' ORDER BY id;

  3

  The fix

  SELECT id FROM tickets WHERE priority IS DISTINCT FROM 'high' ORDER BY id;

  2

  3

  The 'not high priority' queue loses every ticket with a blank priority — WHERE drops the UNKNOWN rows along with the FALSE ones.

• COALESCE / NULLIF mechanics

  COALESCE returns the first non-NULL (order matters); NULLIF nulls out a sentinel value.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE readings(spot VARCHAR, measured INT, estimate INT); INSERT INTO readings VALUES ('s1',NULL,7),('s2',4,9);

  The trap

  SELECT spot, COALESCE(measured, 0, estimate) AS val FROM readings ORDER BY spot;

  s1	0
  s2	4

  The fix

  SELECT spot, COALESCE(measured, estimate, 0) AS val FROM readings ORDER BY spot;

  s1	7
  s2	4

  The reading fell back to 0 instead of the estimate — COALESCE returns the first non-NULL and 0 is a value, so putting 0 before the estimate means the estimate is never reached.

• NULL propagates in expressions

  amount + NULL and string concat with NULL both yield NULL — one NULL poisons the expression.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE batches(grade VARCHAR, barrel VARCHAR); INSERT INTO batches VALUES ('A','12'),('B',NULL);

  The trap

  SELECT grade, grade || '-' || barrel AS label FROM batches ORDER BY grade;

  A	A-12
  B	NULL

  The fix

  SELECT grade, concat(grade, '-', COALESCE(barrel, 'loose')) AS label FROM batches ORDER BY grade;

  A	A-12
  B	B-loose

  The batch label for the NULL barrel came out NULL — the || operator propagates NULL through the whole expression, while concat() skips NULLs and COALESCE supplies a stated default.

• Aggregates ignore NULLs (except COUNT(*))

  SUM/AVG/COUNT(col) skip NULLs; COUNT(*) counts rows — the bridge into P04.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE runs(t INT); INSERT INTO runs VALUES (10),(NULL),(20);

  The trap

  SELECT AVG(t) AS avg_t FROM runs;

  15

  The fix

  SELECT SUM(t) * 1.0 / COUNT(*) AS per_entrant FROM runs;

  10

  AVG read 15 (average of the finishers) where the dashboard meant 10 (average per entrant) — SUM/AVG/COUNT(col) skip NULLs while COUNT(*) counts rows, so the two averages answer different questions the moment a NULL exists.

• IS DISTINCT FROM (null-safe comparison)

  IS DISTINCT FROM treats NULL as a comparable value — two NULLs are not distinct — the null-safe = and <> for keys, change detection, and dedup.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE cat(id INT, old VARCHAR, new VARCHAR); INSERT INTO cat VALUES (1,NULL,'heirloom'),(2,'a','a'),(3,'b',NULL);

  The trap

  SELECT id FROM cat WHERE old <> new ORDER BY id;

  0 rows

  The fix

  SELECT id FROM cat WHERE old IS DISTINCT FROM new ORDER BY id;

  1

  3

  The change scan missed the row where old was NULL and new was 'heirloom' — <> returns UNKNOWN when either side is NULL, so it drops the NULL→value and value→NULL transitions that IS DISTINCT FROM catches.