Problem Statement
Compare NOT EXISTS with NOT IN for anti-joins. When can NOT IN produce surprising results?
Explanation
NOT EXISTS uses a correlated subquery and returns true when no matching row exists. It is NULL safe and stops at the first match. Plans are often efficient on indexed keys.
NOT IN compares a value against a list. If the subquery can return NULL, the comparison becomes unknown and may return no rows at all. Prefer NOT EXISTS for robust results unless you guarantee the subquery column is NOT NULL.
Code Solution
SolutionRead Only
SELECT a.id FROM A a WHERE NOT EXISTS ( SELECT 1 FROM B b WHERE b.a_id = a.id );
