Joining tables

Why the data is in pieces

An order stores customer_id, not the customer's name, city and country. One fact, one place: when Orsenne moves to Manchester you change one row, and every order in history is instantly right. That is the whole argument for splitting data into tables, and join is the cost of it — a small, well-understood cost.

the id in one table points at the key of another

orders.customer_id  →  customers.id
order_items.order_id →  orders.id
order_items.door_id  →  doors.id
jobs.fitter_id       →  fitters.id

The join, and the on clause

Read it as: for every row of orders, find the rows of customers where the on condition is true, and glue them together. o and c are aliases — once a query has two tables, every column should say which one it came from, and an alias makes that short.

twenty-four orders, each with its customer beside it

select o.id, o.placed, o.status, c.name, c.city
from   orders o
join   customers c on c.id = o.customer_id;

join means inner join

The word inner is optional and almost never written. It means: a row survives only if the other side has a match. An order whose customer had been deleted would vanish from this result — which is one reason to let the database forbid that, in the Keys lesson.

Three tables is the same move, twice

Forty rows come back — one per line of every order, not one per order. Joining to a "many" table multiplies rows, and that is the single most important thing to keep in your head: after joining order_items, count(*) counts lines, and sum(o.total) would count an order's total once per line it has.

what was actually ordered, in words

select c.name    as customer,
       o.placed,
       d.name    as door,
       i.qty,
       i.unit_price,
       i.qty * i.unit_price as line_total
from   orders o
join   customers c   on c.id = o.customer_id
join   order_items i on i.order_id = o.id
join   doors d       on d.id = i.door_id
order by o.placed, d.name;

Joining, then aggregating

revenue per door: group after the join

select   d.name,
         sum(i.qty)                          as sold,
         round(sum(i.qty * i.unit_price), 2) as revenue
from     doors d
join     order_items i on i.door_id = d.id
group by d.id, d.name
order by revenue desc
limit    3;

-- Calastra 24550 · Caivor 21380 · Vesper 20720

on versus where

Put the relationship in on and the filtering in where: the query then says which part is structure and which part is the question. For an outer join the two are emphatically not the same, and the next lesson is built on that difference.

for an inner join these are the same query

from orders o join customers c on c.id = o.customer_id and c.country = 'AZ'
from orders o join customers c on c.id = o.customer_id where c.country = 'AZ'

using and natural join

join customers using (id) works when both columns have the same name, and natural join guesses by matching every same-named column — including name, city or created_at, which is how a natural join silently returns nothing. Write the on.

The join that is not a mistake

If the on condition is missing or always true, every row of one table pairs with every row of the other. Twelve customers and ten doors give a hundred and twenty rows. That is a cross join, it is occasionally exactly what you want, and it is covered two lessons from now — but if a query suddenly returns far too many rows, look at the on first.