join

Three-Table JOIN: Customers, Orders, and Products

Chain INNER JOINs across the store schema to show what each customer bought and for how much.

Overview

Real queries rarely stop at two tables. Chaining JOINs lets you walk a foreign key path: customers → orders → products. Each join adds one more related table's columns to the result. Here we enrich each order with the customer's name and the product's name and price.

The Query

Store (Customers/Orders/Products)

Loading database engine...

Step-by-Step Breakdown

FROM orders o — start at the fact table (the join hub).
INNER JOIN customers c ON o.customer_id = c.id — attach the buyer.
INNER JOIN products p ON o.product_id = p.id — attach the purchased item.
o.quantity * p.price AS line_total — a computed column available only after both joins bring the two values into the same row.

Pick a good anchor table

Starting from the table at the center of the relationship (here orders) usually makes the join chain shortest and clearest.

Variations

Restrict to completed orders only:

Store (Customers/Orders/Products)

Loading database engine...

Common Mistakes

Wrong join column. Joining orders.customer_id to products.id produces nonsense — always match a foreign key to the referenced primary key.
Ambiguous id. All three tables have an id column; always qualify it (o.id, c.id, p.id).
Forgetting a join multiplies rows. Omitting one ON turns the chain into a cross join of the remaining tables.