NULL

Draft

NULL means missing or unknown. That makes it different from an empty string, zero, or false, and it changes how comparisons work.

Find missing values

Use IS NULL because NULL means unknown, not equal to a value.

SELECT id, title
FROM tickets
WHERE assignee IS NULL;

id	title
2	Write docs

Seq Scan on tickets  (cost=0.00..18.10 rows=4 width=36)
  Filter: (assignee IS NULL)

COALESCE returns the first non-NULL value from its arguments.

SELECT title, COALESCE(assignee, 'Unassigned') AS owner
FROM tickets
ORDER BY id;

Sort  (cost=57.23..59.26 rows=810 width=68)
  Sort Key: id
  ->  Seq Scan on tickets  (cost=0.00..18.10 rows=810 width=68)

A comparison with NULL does not return true, so this query finds nothing.

SELECT id, title
FROM tickets
WHERE assignee = NULL;

No rows

Result  (cost=0.00..0.00 rows=0 width=0)
  One-Time Filter: false