MySQL: Finding Records in One Table That Are Not in Another Table
Writing an anti-join in SQL—selecting all rows from Table A that have no matching foreign key in Table B—is one of those everyday queries where three syntactically valid approaches can exhibit drastically different execution plans and NULL semantics.
1. The Classic LEFT JOIN ... WHERE IS NULL
SELECT a.id, a.email FROM users a LEFT JOIN subscriptions b ON a.id = b.user_id WHERE b.user_id IS NULL;
When b.user_id is indexed, MySQL's optimizer recognizes the Not exists optimization and stops scanning the index on subscriptions as soon as it finds the first matching row for a given a.id.
2. Correlated NOT EXISTS
SELECT a.id, a.email FROM users a WHERE NOT EXISTS ( SELECT 1 FROM subscriptions b WHERE b.user_id = a.id );
Unlike NOT IN, which returns an empty result set if the subquery ever produces a single NULL value, NOT EXISTS handles nullable columns safely and maps directly to a semi-join anti-match in modern MySQL 8.0+ query planners.