SQL Multiple JOINs: Join Three or More Tables

Real questions often span more than two tables: which customer bought which product? needs customers, orders, the items in each order, and products. You answer it by chaining joins, one table at a time.

The example tables#

customers: customer_id, name, city

orders: order_id, customer_id, amount

order_items: one row per product in an order

products: product_id, name, category, price

order_items is a linking table (also called a junction or bridge table). Orders and products have a many-to-many relationship (an order holds many products, and a product appears in many orders), and the linking table connects them.

Chaining joins#

Each JOIN adds one table, connected by its own ON condition:

SELECT o.order_id,
       c.name AS customer,
       p.name AS product,
       oi.quantity
FROM orders o
JOIN customers c    ON c.customer_id = o.customer_id
JOIN order_items oi ON oi.order_id   = o.order_id
JOIN products p     ON p.product_id  = oi.product_id
ORDER BY o.order_id, p.name;

order_id | customer | product    | quantity
---------+----------+------------+---------
101      | Asha     | Headphones | 1
101      | Asha     | Notebook   | 5
102      | Asha     | Notebook   | 2
103      | Ravi     | Bookshelf  | 1
103      | Ravi     | Desk Chair | 1

Read it as a path: orders → their customer → their items → each item's product. Every join condition connects the new table to one that is already in the query.

Order 104 is missing: its customer does not exist, and an inner join needs a match in every table.

Mixing INNER and LEFT joins#

To keep order 104, make the customer join a LEFT JOIN:

SELECT o.order_id,
       COALESCE(c.name, 'Unknown') AS customer,
       p.name AS product,
       oi.quantity
FROM orders o
LEFT JOIN customers c ON c.customer_id = o.customer_id
JOIN order_items oi   ON oi.order_id   = o.order_id
JOIN products p       ON p.product_id  = oi.product_id
ORDER BY o.order_id, p.name;

order_id | customer | product    | quantity
---------+----------+------------+---------
101      | Asha     | Headphones | 1
101      | Asha     | Notebook   | 5
102      | Asha     | Notebook   | 2
103      | Ravi     | Bookshelf  | 1
103      | Ravi     | Desk Chair | 1
104      | Unknown  | Headphones | 1

Aggregating across several tables#

How much has each customer spent, based on the items they bought?

SELECT c.name,
       SUM(oi.quantity * p.price) AS spent
FROM customers c
JOIN orders o       ON o.customer_id = c.customer_id
JOIN order_items oi ON oi.order_id   = o.order_id
JOIN products p     ON p.product_id  = oi.product_id
GROUP BY c.customer_id, c.name
ORDER BY spent DESC;

name | spent
-----+------
Ravi | 11500
Asha | 2920

Ravi: 7,000 + 4,500. Asha: 2,500 + 5 × 60 + 2 × 60.

The double-counting trap#

Here is a subtle and very common bug. Suppose you want each customer's total order amount, but your query also joins order_items (perhaps because another column needs it):

-- Wrong: amounts are counted once per item
SELECT c.name, SUM(o.amount) AS total
FROM customers c
JOIN orders o       ON o.customer_id = c.customer_id
JOIN order_items oi ON oi.order_id   = o.order_id
GROUP BY c.name;

name | total
-----+------
Asha | 620
Ravi | 1120

The correct totals are 370 and 560. Order 101 has two items, so after joining it appears in two rows, and its amount of 250 is added twice. Order 103 also has two items. This is called fan-out: joining to a "many" table multiplies the rows of the "one" side.

The fix is to aggregate at the right level: do not join tables you do not need, or aggregate each "many" table in a subquery or CTE before joining:

SELECT c.name, SUM(o.amount) AS total
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.name
ORDER BY total DESC;

name | total
-----+------
Ravi | 560
Asha | 370

Tips for readable multi-table queries#

• Start FROM the table that represents what you are counting or listing.
• Join in a logical path, one hop at a time.
• Use short, meaningful aliases (o, oi, p), and prefix every column.
• Align the ON clauses so the structure is visible at a glance.

Common mistakes#

Practice#

SELECT p.name,
       COALESCE(SUM(oi.quantity), 0) AS units_sold
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.product_id
GROUP BY p.product_id, p.name
ORDER BY units_sold DESC, p.name;

name       | units_sold
-----------+-----------
Notebook   | 7
Headphones | 2
Bookshelf  | 1
Desk Chair | 1
Laptop     | 0
Monitor    | 0

Key takeaway#