Chapter 3 · 8 min
Join keys and inner joins
A join combines rows from two tables using a condition. An inner join keeps only the pairs where the condition is true, so the join key is the whole question: get it wrong and the query is wrong in a way that still looks plausible.
SELECT o.order_id, o.order_date, oi.product_id, oi.quantity
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.status = 'paid';
Name the tables and alias them. ON states the relationship explicitly; USING (column) is
available only when both sides share the exact column name. Do not put the join condition in
WHERE: it still works for an inner join, but it hides the relationship and changes the meaning of
an outer join, which the next lesson covers.
Keys are not always one column
order_items has a composite primary key of (order_id, product_id), so joining it to orders
matches on order_id only and returns one row per product in each order. That is correct when the
question is about line items. It is wrong when the question is about orders, because the same order
now appears several times. Know the cardinality of the relationship before deciding what a result
row means.
Join keys should be stable identifiers, not descriptive text. Joining on a customer’s email works until two customers share an email or one changes it. The schema’s foreign keys already state the intended relationship; use them.
Join order is not a guarantee
SQL does not promise that rows come back in join order. The planner chooses an algorithm based on
statistics, so a query that happens to return rows in a convenient order on one dataset may return
them differently on another. Add an explicit ORDER BY when order matters, and do not rely on the
order of the tables in FROM.
A join is not a filter
An inner join can remove rows: an order with no matching line item disappears. That is a consequence
of the join, not a WHERE predicate, and it belongs in the explanation. If the question asks for
“orders and their items”, the inner join is right. If it asks for “all orders, with items where they
exist”, the inner join silently drops data and the answer needs an outer join instead.
What to check before writing
Name the relationship, its cardinality, and whether unmatched rows should survive. Those three facts
determine the join type and whether the result needs deduplication. Writing the ON clause before
answering them is how a query ends up correct in syntax and wrong in meaning.