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.
Lesson 04
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.
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.
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.
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.
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.
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.
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).
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.
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:
| Query | Rows |
|---|---|
customers | 22 |
customers JOIN orders | 50 |
customers JOIN orders JOIN order_items | 60 |
customers LEFT JOIN orders LEFT JOIN order_items | 62 |
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.
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.
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.
FROM employees JOIN employees ON ... fails with table name "employees" specified more than once. Each copy of a table needs its own alias.
e, d, l, o, oi, p) and keep them consistent across your queries.ON keywords so the chain is visible at a glance.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.
JOIN attaches one more table with its own ON condition, and can reference any table joined above it.order_items connect many-to-many relationships; join through them.COUNT(DISTINCT ...) or drop unneeded joins to avoid overcounting.LEFT JOIN, any join that depends on the optional table must also be a LEFT JOIN, or the unmatched rows disappear.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.
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.
The measure (quantity times price) lives on order_items. Starting there keeps one row per sale; categories and products only add labels.
Concept check
3 quick questions. One at a time. Instant score at the end.
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.
No results yet
Run a query to see results here
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.
No results yet
Run a query to see results here
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.
No results yet
Run a query to see results here
Final checkpoint
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?