🎓 A1Learner
🔍

SQL Multiple Joins

Multiple joins are a chain of joins that links three or more tables in one query. Each JOIN in the chain adds one table and names its match with its own ON.

Order lines with both names

Two joins run in one query here. Each order line gains a customer name and a product name.

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

A chain with a filter

WHERE filters the chained rows as before. Only the Electronics order lines stay in this result.

SELECT orders.order_id, customers.name AS customer, products.name AS product
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id
JOIN products ON orders.product_id = products.product_id
WHERE products.category = 'Electronics'
ORDER BY orders.order_id;

Revenue per category

Grouping runs over the joined rows here. SUM adds price times quantity for the order lines in each category.

SELECT products.category, ROUND(SUM(products.price * orders.quantity), 2) AS revenue
FROM orders
JOIN products ON orders.product_id = products.product_id
GROUP BY products.category
ORDER BY revenue DESC;

Example

SELECT customers.name AS customer, ROUND(SUM(products.price * orders.quantity), 2) AS revenue
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id
JOIN products ON orders.product_id = products.product_id
GROUP BY customers.customer_id, customers.name
ORDER BY revenue DESC, customers.name;

Key points

  • A chain holds one JOIN for each extra table.
  • Each ON names the match for the table its JOIN adds.
  • The orders table links customers and products, since each order holds both ids.
  • Revenue for one order line is the product price times the quantity.
  • GROUP BY can group rows that a chain of joins produced.

In this example, the first join adds customer names to the orders. The second join adds product prices to the same rows. GROUP BY gathers the rows per customer, and SUM totals price times quantity inside each group. Ben Carter holds the highest revenue at 99.98.

Try it Yourself

Edit the code, then press Run.

Running is simulated · DB Fiddle

Exercise

How many JOIN clauses link three tables in one chain?