IS NULL / IS NOT NULL
The only correct way to test for NULL. = and <> return unknown when either side is NULL, and unknown is not true, so those rows are filtered out of WHERE. IS NULL returns a real boolean and behaves the way you expect.
Syntax #
sql
SELECT *
FROM table_name
WHERE col IS NULL;
SELECT *
FROM table_name
WHERE col IS NOT NULL;Example #
Loading SQL editor...
Warning
**= NULL always returns unknown, never true.** The query runs with no error and the row is silently excluded from WHERE. This is the single biggest source of "why does my query return zero rows" confusion. Always use IS NULL / IS NOT NULL. For equality that treats NULL = NULL as true, use IS NOT DISTINCT FROM (Postgres, DuckDB) — handy in MERGE / JOIN conditions where you need two nullable columns to match as "both missing."