Explain what this query returns:
SQLLevel: IntermediateJOINs Deep Dive
Explain what this query returns:
SELECT a.id, b.id
FROM table_a a
LEFT JOIN table_b b ON a.id = b.id
WHERE b.id IS NULL;
Hints
- Write the join keys and relationship cardinality.
- What happens to duplicates and unmatched rows? Check WHERE versus ON.
Answer
This is an anti-join — it returns rows from table_a that have NO matching row in table_b. The LEFT JOIN brings in all rows from table_a, and WHERE b.id IS NULL filters to only the ones where no match was found.
This pattern answers "find X that doesn't have a corresponding Y." For example, "find customers who have never placed an order."
Source and licence: SQL Interview Questions — Arda Kocadoru · MIT