Anti-Joins (NOT EXISTS)
Overview
An Anti-Join is a query designed specifically to find 'Orphans'—rows in Table A that have absolutely NO matching records in Table B. Examples include finding 'Users who have never logged in' or 'Products that have never been purchased'. While you can achieve this with a LEFT JOIN ... WHERE B.id IS NULL, using NOT EXISTS is semantically cleaner and mathematically safer (immune to the NULL traps of NOT IN).
Syntax
-- The NOT EXISTS Anti-Join (The Gold Standard)
SELECT u.username, u.email
FROM users u
WHERE NOT EXISTS (
-- Checks the orders table for a match.
-- If NO match is found, the outer user row is kept!
SELECT 1
FROM orders o
WHERE o.user_id = u.id
);Common Pitfalls
- Using
NOT INinstead ofNOT EXISTS. As stated previously, if the inner query of aNOT INclause happens to return aNULLvalue, the boolean logic evaluates to 'Unknown', and the entire outer query fails to return ANY rows.NOT EXISTSis completely immune to thisNULLcorruption. - Running Anti-Joins on unindexed foreign keys.
NOT EXISTSrequires the database to search Table B for a match. If Table B'suser_idcolumn doesn't have a B-Tree Index, the database has to perform a Full Table Scan for every single user, destroying performance.
Interview Questions
1. The LEFT JOIN / IS NULL method (Join the tables, then filter for NULLs on the right side). 2. The NOT EXISTS subquery method. Modern query optimizers usually compile both into the exact same execution plan, but NOT EXISTS is generally preferred for readability.
Real-World Example
A cleanup script to find and delete abandoned shopping carts that have zero items in them.
DELETE FROM shopping_carts c
WHERE NOT EXISTS (
SELECT 1
FROM cart_items i
WHERE i.cart_id = c.id
);Check Your Knowledge
Test your understanding of Anti-Joins (NOT EXISTS) with these quick questions.