Multiple Joins, Join Conditions and Aliases

Concept

Real queries join more than two tables. The mechanics do not change - each JOIN adds one table and one ON clause - but readability and correctness both depend on discipline.

Syntax

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

Example

SELECT c.name            AS customer,
       c.country,
       o.id              AS order_id,
       o.order_date,
       p.name            AS product,
       p.category,
       oi.quantity,
       oi.unit_price,
       oi.quantity * oi.unit_price AS line_total
FROM   customers   c
JOIN   orders      o  ON o.customer_id = c.id
JOIN   order_items oi ON oi.order_id   = o.id
JOIN   products    p  ON p.id          = oi.product_id
WHERE  o.status = 'shipped'
  AND  o.order_date >= '2024-01-01'
ORDER BY c.name, o.order_date, p.name;

Explanation

Read the chain as a path: customer to order to line item to product. Each join has one condition and links the new table to a table already in the query. Four tables need three join conditions - if you have four tables and two conditions, one join is a Cartesian product.

The grain of the result is one row per order line, not per order and not per customer. Getting this wrong is what produces inflated sums: SUM(o.total) in this query would add each order's total once per line item.

Join conditions beyond equality

-- Compound key: both columns must match
JOIN order_items oi ON oi.order_id = o.id AND oi.product_id = p.id

-- Range join: match a value into a band.
-- Assumes a lookup table salary_bands(band_name, min_salary, max_salary).
SELECT e.first_name, e.salary, b.band_name
FROM   employees e
JOIN   salary_bands b
       ON e.salary >= b.min_salary
      AND e.salary <  b.max_salary;

-- Extra constant in the ON clause of an outer join
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'shipped'

Important rules

• n tables require at least n - 1 join conditions.
• Every joined table must connect to something already in the query, directly or through another join.
• For inner joins the written order does not affect the result. For outer joins it does.
• The grain of the result is set by the most detailed table in the chain. Know it before you aggregate.
• An ON clause may contain any condition, not only equality - but non equality joins are much harder to index.

Common mistakes

• Summing a parent table's column after joining to a child table, inflating the total by the number of children.
• Missing a join condition somewhere in a long chain and getting a silent partial Cartesian product.
• Using table aliases like t1, t2, t3 and making the query unreviewable.
• Joining through a table that is not actually needed, adding cost and duplicate rows for nothing.

Best practices

• One join per line, with its ON clause on the same line or immediately below.
• Alias tables by initials of the real name: c, o, oi, p.
• Order the joins to follow the logical path through the data model.
• State the grain in a comment at the top of any complex query: -- one row per order line.
• Check the row count against a simpler query before trusting the aggregates.

Practice

1. Return customer name, order date and total line value for every shipped order line in 2024.
2. Add the department name and location to a query that already joins employees to projects through assignments.
3. Explain why SUM(o.total) is wrong in the four table example above, and write the correct total two ways.