Anti Join

Definition

An anti join returns rows from one table that have NO matching row in another — the formal name for the LEFT JOIN ... WHERE right.key IS NULL pattern used repeatedly throughout this course (5.11, 7.11, 12.10).

Running example — departments(id, name) and employees(id, name, department_id, manager_id, salary), where department_id/manager_id can be NULL:

departments: (1, Engineering), (2, Sales), (3, Marketing)

employees: (1, Asha, dept=1, mgr=NULL, 90000), (2, Rohan, dept=1, mgr=1, 70000), (3, Neha, dept=2, mgr=1, 65000), (4, Vikram, dept=NULL, mgr=1, 50000)

Note: Marketing (dept 3) has no employees; Vikram has no department; Asha has no manager.

sql
SELECT d.id, d.name
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id
WHERE e.id IS NULL;

This returns Marketing — the one department with zero employees. An anti join can also be expressed with NOT EXISTS (17.6), which is often preferred for correctness (it avoids the NOT IN + NULL gotcha from Chapter 7) and can be at least as efficient, since the engine can stop checking as soon as it confirms no match exists.

Edge Cases and Pitfalls

• The WHERE e.id IS NULL MUST check a column that's actually NULL only when unmatched — using a column that could ALSO be genuinely NULL in a matched row (rather than a NOT NULL column like a primary key) would incorrectly include matched-but-NULL rows as if they were unmatched.
• NOT IN (subquery) is a tempting-looking alternative to an anti join, but suffers the well-known NULL-poisoning gotcha (Chapter 7) — NOT EXISTS or the LEFT JOIN ... IS NULL pattern are the safe choices.
• Anti joins answer "which X have NONE of Y" — the conceptual opposite of a semi join (16.13), which answers "which X have AT LEAST ONE of Y."

Key Takeaways

• Anti join finds rows with no match in another table — implemented via LEFT JOIN + IS NULL, or NOT EXISTS.
• Always check a column guaranteed non-NULL when matched (like a primary key), or the IS NULL test can misfire.
• Prefer NOT EXISTS/anti join over NOT IN with a subquery, due to NOT IN's NULL-related gotcha.