INNER JOIN vs OUTER JOIN

Problem Explain the difference between an INNER JOIN and an OUTER JOIN (LEFT / RIGHT / FULL) in SQL, and say which is typically faster and why.

Schema

• customers(customer_id, name) — some customers have placed no orders
• orders(order_id, customer_id, amount) — joined to customers on customer_id

Example

• customers = {1: Asha, 2: Ravi}; orders = {(10, 1, 250)}
• Work through what rows customers INNER JOIN orders versus customers LEFT JOIN orders each produce, then reason about which one the optimizer can execute more cheaply.