Rows of table A with no match in table B — give two different ways
Tables a(id) and b(a_id). Return every row of a that has no matching row in b — an anti-join. Give two independent queries that produce the same result using different mechanisms.
-- way 1 and way 2: rows of a not present in bWrite both queries.
Way one — LEFT JOIN b ON b.a_id = a.id WHERE b.a_id IS NULL, keeping left rows whose right side stayed unmatched. Way two — WHERE NOT EXISTS (SELECT 1 FROM b WHERE b.a_id = a.id). Prefer NOT EXISTS to NOT IN, which breaks if the subquery returns a NULL.
- ✗Reaching for INNER JOIN, which returns the matches instead of the misses
- ✗Assuming NOT IN and NOT EXISTS behave identically when a NULL is present
- ✗Filtering on IS NOT NULL of the wrong side and inverting the result
- →Why does a NULL in the subquery break NOT IN but not NOT EXISTS?
- →How does the planner typically execute the LEFT JOIN / IS NULL form?
Both queries return the rows of a absent from b, by two different mechanisms.
-- Way 1: outer join, keep the rows that stayed unmatched
SELECT a.*
FROM a
LEFT JOIN b ON b.a_id = a.id
WHERE b.a_id IS NULL;
-- Way 2: correlated NOT EXISTS
SELECT a.*
FROM a
WHERE NOT EXISTS (SELECT 1 FROM b WHERE b.a_id = a.id);Prefer NOT EXISTS over NOT IN (SELECT a_id FROM b): if any b.a_id is NULL, the NOT IN comparison becomes UNKNOWN for every outer row and the query returns nothing. NOT EXISTS is null-safe and usually plans the same as the outer-join form.
