SQL: Join Multiple Tables

To join three or more tables, add one JOIN ... ON ... for each table, each connecting to a table already in the query. Real questions, like "revenue by category" or "what did each customer buy?", almost always need more than two tables.

Syntax

SELECT ...
FROM table_a a
JOIN table_b b ON b.a_id = a.id
JOIN table_c c ON c.b_id = b.id
JOIN table_d d ON d.id = c.d_id;

The ShopKart path from a customer to a category is:

customers → orders → order_items → products → categories

Examples

1. Three tables: who bought what

SELECT o.order_id,
       c.customer_name,
       oi.product_id,
       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
WHERE o.order_id IN (1001, 1003)
ORDER BY o.order_id;

Result:

(4 rows)

2. Four tables: full purchase history

SELECT c.customer_name,
       o.order_date,
       p.product_name,
       oi.quantity,
       o.status
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
WHERE c.customer_name = 'Aarav Sharma'
ORDER BY o.order_date;

Result:

(7 rows)

3. Five tables: revenue by category from delivered orders

SELECT cat.category_name,
       COUNT(DISTINCT o.order_id) AS orders,
       ROUND(SUM(oi.quantity * oi.unit_price * (1 - oi.discount_pct / 100)), 2) AS net_revenue
FROM order_items oi
JOIN orders o       ON o.order_id = oi.order_id
JOIN products p     ON p.product_id = oi.product_id
JOIN categories cat ON cat.category_id = p.category_id
WHERE o.status = 'Delivered'
GROUP BY cat.category_name
ORDER BY net_revenue DESC;

Result:

(6 rows)

This is the kind of query behind a category performance dashboard. COUNT(DISTINCT o.order_id) matters: an order with two Electronics items should count once.

4. Revenue by customer city

SELECT c.city,
       ROUND(SUM(oi.quantity * oi.unit_price * (1 - oi.discount_pct / 100)), 2) AS net_revenue
FROM customers c
JOIN orders o       ON o.customer_id = c.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.status = 'Delivered'
GROUP BY c.city
ORDER BY net_revenue DESC
LIMIT 5;

Result:

(5 rows)

The double-counting trap

Joining a one-to-many relationship repeats the "one" side. Suppose each order had a ₹49 delivery fee and you summed it after joining to order_items:

SELECT COUNT(*) AS rows_after_join,
       COUNT(DISTINCT o.order_id) AS actual_orders,
       COUNT(*) * 49 AS wrong_fee_total,
       COUNT(DISTINCT o.order_id) * 49 AS right_fee_total
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id;

Result:

(1 row)

38 rows but only 24 orders. Any order-level number summed at line level is inflated. Fixes:

• Aggregate each table to the right level first (with a CTE or subquery), then join.
• Use COUNT(DISTINCT ...) for counts.

Common mistakes

• A table that doesn't connect. Every joined table needs an ON condition linking it to the tables before it.
• Mixing LEFT and INNER joins carelessly. An inner join after a left join can silently drop the rows the left join kept.
• Not checking row counts. Run COUNT(*) after each join you add; a sudden jump means a many-to-many match.

Practice question

Show the total units sold per brand for delivered orders, highest first. Show brand as "Unbranded" if it's missing.

SELECT COALESCE(p.brand, 'Unbranded') AS brand,
       SUM(oi.quantity) AS units_sold
FROM order_items oi
JOIN orders o   ON o.order_id = oi.order_id
JOIN products p ON p.product_id = oi.product_id
WHERE o.status = 'Delivered'
GROUP BY COALESCE(p.brand, 'Unbranded')
ORDER BY units_sold DESC, brand
LIMIT 5;

Key takeaways

• Add one JOIN ... ON ... per table; each must link to the query so far.
• Follow the key path between tables (customers → orders → order_items → products → categories).
• One-to-many joins repeat rows, so watch for double counting.
• Use COUNT(DISTINCT ...) or pre-aggregate before joining.