Home › Blog › SQL Anti-Join

The anti-join — "in A but not in B", three ways, one famous trap

Published Aug 2, 2026·7 min read·Every query runnable in your browser

"Which customers have never ordered?" is the anti-join: rows in A with no match in B. There is no ANTI JOIN keyword in standard SQL — you spell it with LEFT JOIN + IS NULL, NOT EXISTS, or NOT IN. On SQL Quest's data all three return the same 6 customers… until a NULL sneaks into the NOT IN list, and that one silently returns nothing. All three spellings, then the trap.

Spelling 1 — LEFT JOIN … IS NULL

Keep everyone, then keep only the NULL-padded rows — 6 customers
SELECT c.name, c.membership
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

The logic reads off the LEFT JOIN contract: every customer survives the join; the ones with no orders got NULL padding; selecting IS NULL keeps exactly the unmatched. Visual, easy to extend with columns from either table, and the spelling most interviewers expect first.

Spelling 2 — NOT EXISTS

The same 6, as a direct statement of the question
SELECT c.name, c.membership
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

Reads exactly like the question — "customers for whom no order exists." Two engineering advantages: it short-circuits (one match is enough to reject a customer), and it is NULL-proof — which is about to matter.

Spelling 3 — NOT IN, and the trap

Works here (6 rows) — because this subquery happens to have no NULLs
SELECT name FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);

One NULL in that subquery and this returns ZERO rows — silently. NOT IN (1, 2, NULL) expands to x != 1 AND x != 2 AND x != NULL; the last term is unknown, unknown poisons the AND chain, no row is ever true. It works on this dataset only because orders.customer_id has no NULLs. Foreign keys drift, schemas change — and the day one NULL appears, your report goes quietly empty. The mechanism in depth: IS NULL vs = NULL.

If you use NOT IN at all, guard it: NOT IN (SELECT customer_id FROM orders WHERE customer_id IS NOT NULL). Or just use NOT EXISTS and never think about it again.

Which spelling to pick

Interview tell: the follow-up to any anti-join answer is "what happens if the subquery returns a NULL?" Candidates who answer it before being asked stand out. The reverse question — rows in A with a match, called the semi-join — is EXISTS, same skeleton without the NOT.

▶ Practice: LEFT JOIN — Watch the NULLs Appear

FAQ

Which anti-join is fastest?

Modern optimizers (Postgres, SQL Server, MySQL 8+) usually compile NOT EXISTS and LEFT JOIN…IS NULL to the same anti-join plan. NOT IN can be slower and carries the NULL bug — it loses on both axes.

Is there an actual ANTI JOIN keyword anywhere?

In SQL dialects of the lake/warehouse world — Spark SQL and Databricks have LEFT ANTI JOIN as real syntax. Standard SQL and the OLTP engines don't; you spell it with the three patterns above.

How do I anti-join on two columns?

NOT EXISTS handles it naturally: correlate on both columns in the subquery's WHERE. This is another place NOT IN struggles — multi-column NOT IN is awkward-to-impossible depending on the engine.

Related reads & drills

"In A but not in B" — now make it automatic.

The JOIN track runs from your first LEFT JOIN to the Hard anti-join pipeline, on real tables, with a Coach that shows which rows matched and which vanished.

Practice JOINs — Free →