SQL Anti-JOIN — Find Rows with No Match

What Is an Anti-JOIN?

An anti-join returns rows from the left table that have no match in the right table. SQL does not have an explicit ANTI JOIN keyword, but you can achieve it with three equivalent patterns.

Pattern 1: LEFT JOIN + IS NULL (Most Common)

SELECT c.id, c.name
FROM customers AS c
LEFT JOIN orders AS o ON c.id = o.customer_id
WHERE o.customer_id IS NULL;

This keeps all customers, then filters to only those where the join produced NULL — meaning no order exists for that customer.

Pattern 2: NOT EXISTS (Often Fastest)

SELECT c.id, c.name
FROM customers AS c
WHERE NOT EXISTS (
  SELECT 1 FROM orders AS o
  WHERE o.customer_id = c.id
);

NOT EXISTS can short-circuit: it stops searching the orders table as soon as it finds one match, making it efficient when matches are common.

Pattern 3: NOT IN (Simple but Risky)

SELECT c.id, c.name
FROM customers AS c
WHERE c.id NOT IN (
  SELECT customer_id FROM orders
);

Warning: If orders.customer_id contains any NULL values, NOT IN returns an empty result because x NOT IN (..., NULL, ...) is always UNKNOWN. Use NOT EXISTS or add WHERE customer_id IS NOT NULL to the subquery.

Performance Comparison

Pattern	NULL-safe	Short-circuit	Plan type
LEFT JOIN + IS NULL	Yes	No	Hash/Merge join
NOT EXISTS	Yes	Yes	Anti Semi Join
NOT IN	No*	Depends	Various

Modern PostgreSQL and SQL Server optimizers often produce the same plan for all three. MySQL historically favored NOT EXISTS but has improved in version 8+.

Choosing the Right Pattern

Use NOT EXISTS as the default — it is NULL-safe, clearly expresses intent, and performs well across all major databases. Use LEFT JOIN + IS NULL when you also need columns from the right table for other filtering.