Dither and Bicker

MySQL: Finding Records in One Table That Are Not in Another Table

Published September 16, 2014 by Chris • Database Engineering Archive

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.