Skip to content
Quantoros

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

Related questions