Joins Meet WHERE and GROUP BY

Joins compose with everything you know

A join's output is just rows, so every tool from units 2 through 4 works on it. Filter joined rows with WHERE:

SELECT customers.name, orders.item
FROM orders
JOIN customers ON customers.id = orders.customer_id
WHERE customers.city = 'Tokyo'
ORDER BY orders.id;

The join runs first in your mental model (build the combined rows), then WHERE keeps only the rows whose customer lives in Tokyo. Note the power move here: you filtered orders using a column that only exists in customers.

Join, then group

GROUP BY works on joined rows too. Total spend per customer, by name this time instead of by a repeated name column (compare lesson 4-2):

SELECT customers.name, SUM(orders.price) AS total
FROM orders
JOIN customers ON customers.id = orders.customer_id
GROUP BY customers.name
ORDER BY customers.name;

Build the joined rows, bucket them by name, sum each bucket. This join + GROUP BY combination is probably the single most common query shape in real applications: revenue per product, posts per user, students per course.