Skip to content
Tutorials Center
My learning

Topics

Lesson 41 of 125 ยท Joins

Joining Many Tables

Learn to join three or more tables in SQL, read join chains, mix INNER and LEFT joins safely, and avoid row multiplication (fan-out) that inflates sums and counts.

Real questions rarely live in two tables. "Which categories do customers in Poland buy from?" touches customers, orders, order items, products and categories: five tables in one query. The good news is that you already know everything you need. A query with many joins is just a chain of the INNER and LEFT joins you've used so far, added one at a time.

The hard part isn't the syntax. It's keeping track of what one row means as the chain grows, so that you don't lose rows you wanted or multiply rows you didn't. This lesson walks through a long chain step by step, then covers the three classic traps: the INNER JOIN that cancels a LEFT JOIN, fan-out that inflates totals, and two independent one-to-many joins that multiply each other.

Building a chain one join at a time

The shop database is a typical normalized schema. Each arrow is a foreign key:

TEXT
customers โ† orders โ† order_items โ†’ products โ†’ categories

An order belongs to a customer, an order has several items, and each item points to a product, which belongs to a category. To show the lines of one order with the customer's name, product names and categories, start from the table whose rows you want (one row per item) and join outward:

SQL Five tables, one result ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Order 16 has two items, so the result has two rows: Rosa Cruz bought a Bamboo Cutting Board (Kitchen) and two Folding Camp Chairs (Outdoor). Every join in this chain goes from "many" to "one" (each item has one order, each order one customer, each product one category), so the row count never grows beyond the number of items.

How to read a chain

Logically, a chain is evaluated left to right. The first join combines order_items with orders; the next join combines that result with customers, and so on. That's why each ON clause may refer to any table that appeared earlier, not just the one directly before it: p.category_id in the last join belongs to a table joined two steps earlier.

Keep a running sentence in your head: "one row per order item, plus its order, plus that order's customer, plus the item's product, plus the product's category." If adding a join changes the first part of that sentence (one row per what?), stop and check whether you meant it.

For INNER JOINs, the order you write the tables in doesn't change the result, and the query optimizer is free to execute them in any order it thinks is fastest. You can start from categories and work toward order_items, and you'll get the same rows. Order starts to matter when you mix in outer joins.

Answering a question across the chain

Once the chain is in place, grouping works on the joined rows like on any table. Here is units sold per category to customers in Poland:

SQL Polish customers by category ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

This time the chain starts from customers, because the filter is about customers, but the rows are still one per order item, which is exactly what SUM(oi.quantity) needs. COUNT(DISTINCT o.customer_id) counts people, not rows: one customer usually appears on many item rows.

Many-to-many relationships

In the company database, employees and projects are connected through a junction table, assignments (employee_id, project_id, role, hours_per_week). Reaching a project from an employee takes two joins, and the project's owning department is a third. Notice that departments appears twice with different aliases, because it plays two roles: the employee's department and the project's department:

SQL Helping other departments ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Eleven assignments cross department lines; five of them are people from other departments helping with the Mobile App Rewrite. Joining the same table twice is common in multi-join queries (think from_airport and to_airport, or billing_address and shipping_address), and it only works because each copy has its own alias.

Trap 1: an INNER JOIN after a LEFT JOIN

Suppose you want every employee and the projects they work on, including people with no assignments. You start with a LEFT JOIN to assignments, then join projects to get the names:

SQL The vanishing LEFT JOIN ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The first query returns 39 rows, one per assignment; the second returns 49, because it also keeps the 10 employees who aren't on any project. What went wrong in the first? For an unassigned employee, the LEFT JOIN produces a row with a.project_id = NULL. The next step is an INNER JOIN, and p.id = NULL is never true, so the row is thrown away. The inner join quietly turned the whole chain back into an inner join.

Rule of thumb: once a table is joined with LEFT JOIN, every table that hangs off it must also be LEFT JOINed (or its condition must tolerate NULL). A related mistake is a WHERE condition on the optional table, such as WHERE p.budget > 100000, which removes the NULL rows in the same way; move such conditions into the ON clause if you want to keep unmatched rows.

There is a second fix that keeps the inner join between assignments and projects: put the inner join in parentheses, so it's evaluated first and the result is LEFT JOINed as a unit:

SQL Nested join in parentheses ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Read it as: "employees, optionally matched with (assignments that have a valid project)". The Support team shows Omar Tanaka and Ivo Andersson with NULL projects, and Dina Dubois twice because she has two assignments. Nested joins are standard SQL and work in SQLite, PostgreSQL, MySQL and SQL Server, but many teams find the all-LEFT version easier to read.

Trap 2: fan-out inflates totals

When a join goes from "one" to "many", each row on the "one" side is repeated once per match. That's called fan-out, and it's harmless until you aggregate a column of the "one" side. The orders table has a shipping fee per order. Watch what happens when you sum it after joining the items:

SQL Shipping counted too often ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Customer 21 placed 3 orders and paid 15.00 in shipping. After the join there are 6 rows, one per item, so each order's fee is added once for every item in it and the total comes out as 22.50. Nothing errors; the number is just wrong.

The general fix is to aggregate each table at its own level before joining. If you need item totals and shipping in the same report, compute item totals per order first, then join that one-row-per-order result to orders:

SQL Aggregate first, then join ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The subquery in parentheses (a derived table, covered in Subqueries) returns exactly one row per order, so joining it to orders doesn't multiply anything, and both sums are correct.

Trap 3: two one-to-many joins multiply each other

The nastiest fan-out happens when you join two independent "many" tables to the same parent. A department has many employees and, separately, many projects. Joining both in one chain pairs every employee with every project of the department: a cross join inside each department.

SQL Employees times projects ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Engineering has 10 employees and 2 projects, so the join creates 10 ร— 2 = 20 rows for it, and both naive counts say 20. Every other department owns exactly one project, so its employee count happens to look right, while its project count equals its headcount. A report that only showed headcounts would pass a quick check for five of six departments; that's what makes this bug so easy to miss in testing.

COUNT(DISTINCT ...) repairs counts, but it can't repair sums: SUM(DISTINCT salary) would also drop two different people who earn the same amount. The reliable fix is again to aggregate each branch separately and join the small results, or to use correlated subqueries for each number:

SQL One branch at a time ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Each derived table has one row per department, so the final joins are one-to-one and every number is right. Finance shows a total_budget of 0 because its only project has a NULL budget.

A checklist for long queries

  • Decide the grain first: what does one output row represent? Start FROM the table at that level, or aggregate down to it.
  • Follow the foreign keys: each ON usually equates a foreign key with the primary key it points to. A table that appears in no ON condition is an accidental cross join.
  • Alias every table and qualify every column (o.id, not id). Columns like id and name exist in many tables, and an unqualified one is either an "ambiguous column" error or, worse, the wrong column.
  • Outer joins propagate: after a LEFT JOIN, keep joining its children with LEFT JOIN and put their filters in ON.
  • Watch the row count: run SELECT COUNT(*) after adding each join. A jump you can't explain means fan-out.
  • Aggregate before joining when two branches fan out, or when you sum a column of the "one" side.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. employees LEFT JOIN assignments ... JOIN projects ON p.id = a.project_id: what happens to employees with no assignments?

    The LEFT JOIN keeps them with a.project_id = NULL, but the following INNER JOIN drops those rows. Use LEFT JOIN for projects too, or a nested join.
  2. 2. A department has 4 employees and 3 projects. How many rows does departments LEFT JOIN employees ... LEFT JOIN projects ... produce for it, when both joins match on department_id?

    Two independent one-to-many joins pair every employee with every project: 4 ร— 3 = 12 rows.
  3. 3. Why does SUM(o.shipping) come out too high after orders JOIN order_items?

    This is fan-out. Sum shipping on the orders table alone, or join a result that has one row per order.
  4. 4. In a chain of joins, which tables may an ON clause refer to?

    Each join combines the result so far with one more table, so its condition can use every table already in that result.

Summary

  • A multi-table query is a chain of joins; each join adds one table to the result so far.
  • An ON clause can reference any table joined earlier; aliases are essential, especially when the same table plays two roles.
  • For INNER JOINs, table order doesn't change the result; with outer joins it does.
  • An INNER JOIN (or a WHERE filter) on a LEFT-joined table cancels the LEFT JOIN; keep joining with LEFT JOIN or nest the inner join in parentheses.
  • Joining from "one" to "many" repeats rows (fan-out), which inflates sums of the "one" side.
  • Two independent one-to-many joins multiply each other; aggregate each branch separately before joining.
  • Check the row count after each join you add.

Next: UNION, INTERSECT and EXCEPT, where you'll stack the results of whole queries on top of each other.