Problem Statement
Show how you would express an anti-join using NOT EXISTS and explain why it handles NULLs correctly.
Explanation
Write a correlated subquery in NOT EXISTS that checks for the absence of a matching row. The database evaluates it per outer row and returns true only when the inner query returns no rows.
This approach does not compare values directly, so NULL does not break the logic. It avoids the three valued logic pitfall that can affect NOT IN when the right side has NULLs.
Code Solution
SolutionRead Only
SELECT p.product_id FROM products p WHERE NOT EXISTS ( SELECT 1 FROM sales s WHERE s.product_id = p.product_id );
Practice Sets
This question appears in the following practice sets:
