Skip to content
Quantoros

How does JOIN order affect results?

SQLLevel: IntermediateJOINs Deep Dive

How does JOIN order affect results?

Hints
  • Write the join keys and relationship cardinality.
  • What happens to duplicates and unmatched rows? Check WHERE versus ON.

Answer

For INNER JOIN, order doesn't affect which rows are returned (just potentially which side the columns come from). For LEFT JOIN, the order matters because the left table is always fully preserved.

-- All customers + their orders (customers without orders appear)
SELECT * FROM customers c LEFT JOIN orders o ON c.id = o.customer_id;

-- All orders + their customers (orders without customers appear, customers without orders don't)
SELECT * FROM orders o LEFT JOIN customers c ON o.customer_id = c.id;

Swapping sides on a LEFT JOIN changes who gets the NULLs.

Source and licence: SQL Interview Questions — Arda Kocadoru · MIT

Related questions