Joining Three or More Tables

Real interview problems rarely stop at two tables. You will be asked to join three, four, or more. The key insight that calms the chaos: a multi-table join is just a sequence of two-table joins.

The database joins the first two into an intermediate result, then joins that result to the third, and so on. If you can reason about one join, you can reason about a chain of them.

We extend our example with a third table. Now we have customers, their orders, and the products each order references.

• customers(id, name)
• orders(id, customer_id, product_id, amount)
• products(id, title)

Two relationships connect them: orders.customer_id = customers.id and orders.product_id = products.id.