What is the difference between 'INNER JOIN' and 'OUTER JOIN'?

Instruction: Differentiate between INNER JOIN and OUTER JOIN in SQL.

Context: This question evaluates the candidate's understanding of the different types of JOIN operations and their use cases.

Updated

Official answer

An inner join returns matching pairs. A left outer join additionally returns each unmatched left row with right-side NULLs; right and full outer joins preserve the corresponding side or both sides. Multiple matches still produce multiple rows.

For PostgreSQL:

WITH customers(id) AS (VALUES (1), (2)),
orders(id, customer_id, status) AS (VALUES (10, 1, 'pending'))
SELECT c.id, o.id AS paid_order_id
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.id AND o.status = 'paid'
ORDER BY c.id;

Both customers remain, each with a NULL paid-order ID. The ON condition defines eligible matches. Moving the paid-status condition into WHERE would remove those NULL-extended rows because the comparison is not true. An inner join with the shown ON condition returns no rows.

This distinction matters when a report must include entities with no qualifying activity. Set-overlap pictures alone miss row multiplicity and SQL NULL behavior; reason about matching pairs and post-join filters instead.

Related Questions