Joining three or more tables

Chaining joins

SELECT c.name, p.product_name, li.quantity, o.order_date
FROM orders o
JOIN customers  c  ON c.customer_id = o.customer_id
JOIN line_items li ON li.order_id    = o.order_id
JOIN products   p  ON p.product_id  = li.product_id
WHERE o.order_date >= '2024-01-01';

Joins are applied in order, each working on the accumulated result. Start from the table whose grain you want the output to have — here, one row per line item.

Mixing inner and outer

Order matters when you mix them. An INNER JOIN placed after a LEFT JOIN can eliminate the rows the LEFT JOIN was preserving, because the inner join demands a match.

If you need to preserve rows through a chain, the later joins usually need to be LEFT as well.

Finding a fan-out

In a four-table join returning too many rows, add the joins back one at a time and count after each:

SELECT COUNT(*) FROM orders;                       -- 1,000
-- + customers                                     -- 1,000  fine
-- + line_items                                    -- 3,400  expected fan-out
-- + products                                      -- 6,800  wrong, investigate

The jump tells you which join has duplicate keys on the side you assumed was unique.

Readability

Consistent aliases, one join per line, and the ON condition written with the joined table on the left all help. On anything beyond four tables, a CTE per logical step is usually easier to follow and to debug than one long FROM clause.