Exclusion Joins (Anti-Joins)

These are not a separate join type — they’re a pattern built on top of LEFT or FULL OUTER JOIN. The goal is to find rows that have no match in the other table.

SELECT e.name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
WHERE d.dept_id IS NULL;
-- Result: Carol, David

SELECT d.dept_name
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.dept_id
WHERE e.id IS NULL;
-- Result: Finance

How it works: After a LEFT JOIN, rows with no match on the right side have NULL for all right-table columns. Filtering WHERE right_table.id IS NULL isolates exactly those unmatched rows.