SQL anti-join patterns that don't lie

An anti-join is the SQL way to ask 'rows in A that have no match in B'. There are three reliable patterns and one trap.

1. NOT EXISTS (the recommended default)

SELECT a.*
FROM a
WHERE NOT EXISTS (
  SELECT 1 FROM b WHERE b.key = a.key
);

Handles NULLs correctly. Most planners recognize it as an anti-join and use a hash anti-join under the hood.

2. LEFT JOIN ... IS NULL

SELECT a.*
FROM a
LEFT JOIN b ON b.key = a.key
WHERE b.key IS NULL;

Equally correct, often equally fast. Useful when you also want to project columns from b in matching cases.

3. EXCEPT (when projection matches)

SELECT key FROM a EXCEPT SELECT key FROM b. Concise when you only need one column and you want set semantics (auto-dedupes).

The trap: NOT IN

If the right side contains a single NULL, NOT IN returns no rows. Avoid NOT IN against subqueries unless the column is provably NOT NULL.