Rows missing in another table

The anti-join — customers with no orders, products never sold, records that failed to sync.

The problem

You want the rows in table A that have no match in table B. "Which customers have never ordered?" "Which products have zero sales?"

The pattern: LEFT JOIN … IS NULL

Join, then keep only the rows where the right side came back empty.

SQL playground

Loading editor…

⌘/Ctrl + Enter

Test the right table's column for NULL — ideally a non-nullable one like its primary key, so a legitimately-null column doesn't fake a "no match".

NOT IN is a trap: if the subquery returns a single NULL, the whole result is empty. NOT EXISTS doesn't have this problem. Only reach for NOT IN when the subquery column is guaranteed non-null.

Which to use

	Handles NULLs safely	Readable	Usually fastest
`LEFT JOIN … IS NULL`	yes (test the PK)	ok	yes
`NOT EXISTS`	yes	yes	yes
`NOT IN`	no	yes	varies

LEFT JOIN — the join this pattern is built on.
WHERE — why NULL in a predicate is UNKNOWN, not FALSE.
NULL handling — the three-valued logic behind the NOT IN trap.

Rows missing in another table

The problem

The pattern: LEFT JOIN … IS NULL

The pattern: NOT EXISTS

The pattern: NOT IN (careful)

Which to use

On this page