SQL Anti-Join: NOT EXISTS vs LEFT JOIN vs NOT IN

"Find rows with no match" has three common SQL implementations, and they aren't equally safe. Here's all three side by side, and why NOT EXISTS should usually be your default.

Finding rows that have no match

An anti-join returns rows from one table that have no matching row in another — "departments with zero employees", "customers who never placed an order". SQL has no dedicated ANTI JOIN keyword; instead, there are three common ways to express the same idea, and they don't all behave identically.

Method 1: NOT EXISTS (usually the best default)

A correlated subquery checking for absence.

SELECT d.department_name
FROM departments d
WHERE NOT EXISTS (
  SELECT 1 FROM employees e WHERE e.department_id = d.department_id
);

Method 2: LEFT JOIN + IS NULL

Join, then keep only the rows where nothing matched.

SELECT d.department_name
FROM departments d
LEFT JOIN employees e ON e.department_id = d.department_id
WHERE e.employee_id IS NULL;

Method 3: NOT IN (the risky one)

Works, but only if the subquery's column can never be NULL.

SELECT d.department_name
FROM departments d
WHERE d.department_id NOT IN (
  SELECT department_id FROM employees WHERE department_id IS NOT NULL  -- note the guard
);

Which to use

Method	Correctness	Notes
NOT EXISTS	always correct, NULL-safe	generally the clearest and safest default
LEFT JOIN + IS NULL	correct	familiar if you already think in joins; can return duplicate rows if the join itself is one-to-many
NOT IN	correct ONLY if the subquery column is guaranteed non-NULL	silently returns zero rows for the whole query if even one NULL sneaks into the subquery — see the IN vs EXISTS article

Default to NOT EXISTS. It's NULL-safe by construction (no guard clause needed), reads clearly as "rows where this condition doesn't exist", and modern optimizers handle it efficiently — including on large tables, where it can stop checking a given outer row as soon as one match is found. Reach for NOT IN only when you've explicitly confirmed the subquery's column can't contain NULL.

Frequently asked questions

Is NOT EXISTS always faster than LEFT JOIN + IS NULL?

Not necessarily — modern optimizers often produce equivalent execution plans for both when they're logically the same query. NOT EXISTS's real advantage is correctness and clarity, not a guaranteed performance edge.

Why does my anti-join return duplicate rows with the LEFT JOIN method?

If the joined table has multiple matching rows for a single left-table row in the matched case, a LEFT JOIN can still produce multiple output rows even in the unmatched case if the join condition itself isn't precise — check that the join key is what you expect, and consider NOT EXISTS instead, which doesn't have this failure mode.

Can I use an anti-join with more than one condition?

Yes — NOT EXISTS works with any correlated subquery, so you can add more conditions inside it: WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id AND o.status = 'completed') finds customers with no completed orders specifically.

What's the difference between an anti-join and a semi-join?

A semi-join (EXISTS) returns rows that DO have at least one match, without duplicating rows for multiple matches. An anti-join (NOT EXISTS) is its opposite — rows with NO match at all. They're complementary patterns using the same EXISTS mechanism.