Join concepts

What a join does, matching keys, and why row counts change.

What you'll be able to do

Explain what a join produces
Predict how row counts change
Recognise a fan-out

What a join does

Shaded area shows which rows survive the join.

The ON clause

ON states the matching condition. It is not limited to equality, though equality is by far the most common. Multiple conditions are combined with AND:

ON c.customer_id = o.customer_id
   AND o.status = 'shipped'

Where that second condition goes — ON or WHERE — matters enormously for outer joins, which is covered in the LEFT JOIN lesson.

Row counts and fan-out

One order with three line items becomes three rows. Any SUM of the order total is now tripled.

Fixing a fan-out

Aggregate before joining:

SELECT o.order_id, o.total, li.item_count
FROM orders o
LEFT JOIN (
  SELECT order_id, COUNT(*) AS item_count
  FROM line_items GROUP BY order_id
) li ON li.order_id = o.order_id;

Each order now matches exactly one row, so nothing multiplies.

Key points

A one-to-many join multiplies rows on the "one" side
Always compare row counts before and after a join
Pre-aggregate the many side to avoid a fan-out

Check yourself