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.