Skip to main content
Joins
4 / 4
0%

Joining three or more tables

0/3 solved3 to unlock next
Prev★ Final lesson

Lesson 04

Joining three or more tables

Lesson Description

Combine three or more tables in a safe, stepwise way without introducing duplicate-count bugs. This lesson emphasizes sequencing joins, validating keys, and keeping final output explainable.

Easy Project

Mini project: Build a people-and-departments report that lists employee names, department names, and validates row counts to prove your join is correct.

JOIN Visual

employees

departments

Match keys first, then verify row counts after the join.

When two tables are not enough

Many real questions span a chain of tables. In the HR sandbox, an employee belongs to a department, a department sits at a location, and a location belongs to a region. If your director asks "how many people do we employ in each region, and what do they earn on average?", the answer lives at both ends of that chain: salaries in employees, region names in regions. You have to walk through departments and locations to connect them.

The good news is that you already know everything you need. A query can contain as many joins as you like. Each one attaches one more table to the rows you have built so far, using its own ON condition. This lesson is about doing that cleanly and, more importantly, about the row-count surprises that appear once chains get longer.

Syntax

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

Read it top to bottom: start with table_a, attach table_b, then attach table_c to that result, and so on. Each ON can reference any table that appears above it. You can mix inner and outer joins freely, and you can join the same table more than once as long as each copy has its own alias.

Before writing a long join, sketch the path. For the region question it is:

employees.department_id → departments.id
departments.location_id → locations.id
locations.region_id     → regions.id

Each arrow becomes one JOIN ... ON line.

Worked examples

1. Three tables: employees, departments and locations

Who works in the United Kingdom, and in which city?

SELECT
  e.first_name,
  e.last_name,
  d.name AS department,
  l.city
FROM employees e
JOIN departments d ON d.id = e.department_id
JOIN locations   l ON l.id = d.location_id
WHERE l.country = 'United Kingdom'
ORDER BY l.city, e.last_name;

Ten rows come back: Susan Mavris from Human Resources in London, and nine Sales employees in Oxford. The WHERE clause filters on locations, two hops away from employees. Once a table is joined, its columns are available to every later clause.

2. Four tables, then aggregate

Now the director's question, with the full chain to regions:

SELECT
  r.name AS region,
  COUNT(e.id) AS employees,
  ROUND(AVG(e.salary), 2) AS avg_salary
FROM employees e
JOIN departments d ON d.id = e.department_id
JOIN locations   l ON l.id = d.location_id
JOIN regions     r ON r.id = l.region_id
GROUP BY r.name
ORDER BY employees DESC;

Two rows: the Americas with 30 employees averaging 7633.33, and Europe with 10 employees averaging 10400.00. The regions table has four rows, but Asia and Middle East and Africa have no locations, so the inner joins drop them. If the report must list every region, start from regions and use LEFT JOINs instead, as in example 4.

3. A store revenue report across four tables

Which product categories generated the most revenue from delivered orders?

SELECT
  cat.name AS category,
  SUM(oi.quantity * oi.unit_price) AS revenue,
  COUNT(DISTINCT o.id) AS orders
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products    p  ON p.id = oi.product_id
JOIN categories  cat ON cat.id = p.category_id
WHERE o.status = 'delivered'
GROUP BY cat.name
ORDER BY revenue DESC;

Six rows, led by Electronics at 1085.88 from 5 orders and ending with Sports at 268.96 from 3 orders. Note that revenue is calculated from order_items.unit_price, the price actually paid, not products.price, the current list price.

order_items is a junction table: it sits between orders and products and turns a many-to-many relationship (an order has many products, a product appears in many orders) into two one-to-many relationships. You always join through it; there is no direct key from orders to products.

COUNT(DISTINCT o.id) is deliberate. After joining to order_items, an order with three line items appears three times, so a plain COUNT(o.id) would count line items, not orders. More on that below.

4. A chain of LEFT JOINs

To keep every region, start from regions and make every link optional:

SELECT
  r.name AS region,
  COUNT(l.id) AS location_rows,
  COUNT(d.id) AS departments
FROM regions r
LEFT JOIN locations   l ON l.region_id = r.id
LEFT JOIN departments d ON d.location_id = l.id
GROUP BY r.id, r.name
ORDER BY r.id;

All four regions appear. The Americas show 8 departments, Europe 2, and Asia and Middle East and Africa 0.

Look closely at location_rows, though. The Americas have only 5 locations, but the column says 9. Seattle hosts 5 departments, so after the second join Seattle's location row appears 5 times. That is fan-out in action, and it is why counting the wrong column after several joins gives inflated numbers. If you wanted the true location count here, you would need COUNT(DISTINCT l.id).

5. Joining a table to itself

Every employee except the CEO has a manager_id that points at another row in employees. To show each employee next to their manager, join employees twice with two aliases:

SELECT
  e.first_name || ' ' || e.last_name AS employee,
  m.first_name || ' ' || m.last_name AS manager,
  d.name AS department
FROM employees e
JOIN employees   m ON m.id = e.manager_id
JOIN departments d ON d.id = e.department_id
WHERE e.department_id <> m.department_id
ORDER BY department, employee;

This is a self-join. Without the WHERE, the first join returns 39 rows, one per employee with a manager (Steven King has manager_id = NULL and is dropped). The filter keeps employees whose manager sits in a different department, which returns 16 rows. They are mostly department heads reporting to executives, such as the five Shipping managers who report to Steven King. Aliases are mandatory here: e and m are two independent copies of the same table.

Under the hood: fan-out

Every join multiplies rows by the number of matches on the other side. With one-to-one or many-to-one links (employee to department, department to location) the count stays the same. With one-to-many links (customer to orders, order to items) it grows. Chain two one-to-many joins and it can grow quickly.

Here is the store sandbox at each step:

QueryRows
customers22
customers JOIN orders50
customers JOIN orders JOIN order_items60
customers LEFT JOIN orders LEFT JOIN order_items62

The planner is free to execute joins in any order it likes (it reorders inner joins to find the cheapest plan), but the logical result is always the same. Outer joins constrain that freedom somewhat, which is one reason to use them only where you need them.

Common mistakes

Counting the wrong thing after fan-out

SELECT c.first_name, c.last_name,
       COUNT(o.id) AS orders
FROM customers c
JOIN orders      o  ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
GROUP BY c.id, c.first_name, c.last_name;

Ava Jones shows 5 orders, but she placed 3. Her orders contain 5 line items between them, and the join to order_items repeated each order once per item. Nine customers are overcounted this way. Fix it with COUNT(DISTINCT o.id), or leave out order_items entirely when you do not need it. The same trap applies to SUM: summing an order-level value after joining to items adds it once per item.

An inner join that cancels an earlier LEFT JOIN

SELECT c.first_name, o.id AS order_id, oi.product_id
FROM customers c
LEFT JOIN orders      o  ON o.customer_id = c.id
JOIN      order_items oi ON oi.order_id = o.id;

You wanted every customer, but you get 60 rows covering only 20 customers. The two customers with no orders have o.id = NULL, and the inner join to order_items cannot match NULL, so it drops them. Once a table is optional, every join that hangs off it must also be a LEFT JOIN. With both joins as LEFT JOIN, the query returns 62 rows and keeps everyone.

Reusing an alias or skipping one in a self-join

FROM employees JOIN employees ON ... fails with table name "employees" specified more than once. Each copy of a table needs its own alias.

Readability habits

  • Use short, meaningful aliases (e, d, l, o, oi, p) and keep them consistent across your queries.
  • Put each join on its own line and align the ON keywords so the chain is visible at a glance.
  • Write the joins in the same order as the path you sketched.
  • When a result looks too big, comment out joins from the bottom up and watch where the row count jumps.

Practice

The exercises below this lesson ask you to show employees with both their department and job title, list order line items with the customer's name and the product name, and give each appointment its patient, doctor and department. Before you write each query, sketch the key path from the starting table to every table you need, and check whether any link could multiply rows. Each exercise also specifies an ordering, so read the prompt carefully for which columns to sort by.

Key takeaways

  • Each JOIN attaches one more table with its own ON condition, and can reference any table joined above it.
  • Sketch the foreign-key path first; each hop becomes one join line.
  • Junction tables like order_items connect many-to-many relationships; join through them.
  • One-to-many joins fan out rows, so use COUNT(DISTINCT ...) or drop unneeded joins to avoid overcounting.
  • After a LEFT JOIN, any join that depends on the optional table must also be a LEFT JOIN, or the unmatched rows disappear.

Joining three or more tables: in depth

Real questions usually span more than two tables: revenue by product category needs orders, order lines, products and categories. Chaining joins one table at a time, and checking the row count at each step, is how you build these queries without double counting.

Worked examples

Revenue by category

SELECT c.name AS category,
       SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN products   p ON p.id = oi.product_id
JOIN categories c ON c.id = p.category_id
GROUP BY c.name
ORDER BY revenue DESC;

Starting from the line items keeps one row per sale, so the SUM is not inflated by the extra joins.

Appointments with patient and doctor names

SELECT a.appointment_date,
       pa.first_name || ' ' || pa.last_name AS patient,
       d.first_name  || ' ' || d.last_name  AS doctor,
       a.status
FROM appointments a
JOIN patients pa ON pa.id = a.patient_id
JOIN doctors  d  ON d.id  = a.doctor_id
ORDER BY a.appointment_date;

Two lookups from the same fact table. Distinct aliases (pa, d) keep the two first_name columns apart.

Common mistakes

  • Joining two one-to-many tables to the same parent (orders to both line items and payments) multiplies rows and inflates every sum. Aggregate each side first.
  • Reusing an alias or leaving columns unqualified; with several tables, id and name are almost always ambiguous.
  • Changing one join to LEFT JOIN but leaving later joins as INNER, which drops the rows you meant to keep.

Other databases

  • Join syntax for multiple tables is the same in all major databases.
  • The optimizer reorders joins for speed, so the written order affects readability, not performance, in PostgreSQL.
Check yourself: Why start the revenue query from order_items rather than from categories?

The measure (quantity times price) lives on order_items. Starting there keeps one row per sale; categories and products only add labels.

Concept check

Quick check: Joining three or more tables

3 quick questions. One at a time. Instant score at the end.

Exercise 1

Employee with department and job title

Enterprise Workforce AnalyticseasyPostgreSQL

From the HR sandbox, return first_name, last_name, department name (alias department), and job title (alias job_title) for every employee. Order by last_name.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Exercise 2

Order items: customer, product, quantity

Commerce Operations IntelligenceeasyPostgreSQL

From the store sandbox, join orders, customers, order_items, and products. Return customer first_name, product name, quantity, and unit_price. Order by orders.id, then products.name.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Exercise 3

Appointments with patient, doctor, and department

Clinical Care PerformanceeasyPostgreSQL

From the hospital sandbox, join appointments, patients, doctors, and departments. Return patient first_name, doctor first_name, department name (alias department), and appointment_date. Order by appointment_date, then patients.last_name.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Final checkpoint

End-of-course high-level quiz

Pass mark: 80%. Review each explanation after you submit.

1. Which clause should you use to filter grouped results?

2. What is the safest way to avoid accidental row multiplication in joins?

3. Which feature is best for ranking rows within partitions?

4. Why should final analytical queries usually include ORDER BY?

5. When query logic gets complex, what improves readability most?

Joins
4 / 4
0%

Joining three or more tables

0/3 solved3 to unlock next
Prev★ Final lesson