TopolearnNormalised data lives in many tables; joins are how you put it back together for every real question.
A join pairs rows from two tables according to a condition. The most common form, the inner join, keeps only pairs where the condition is true:
SELECT o.id, c.name, o.total
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id;JOIN means INNER JOIN. The short aliases o and c save typing and become necessary as soon as two tables share column names such as id or name. Qualify every column in a join query; it keeps the query readable and stops it breaking when someone adds a same-named column to the other table.
Think of a join as: for each order, find the customer rows that satisfy ON. Because a customer has many orders, the customer's columns repeat once per order. Joins multiply rows: a row matching three rows on the other side appears three times. That is exactly right for orders and customers, and the cause of most "my totals are too big" bugs when you join two independent one-to-many tables (say, order items and payments) to the same order and then SUM.
Chaining joins walks the schema. To list what each customer bought: customers to orders to order_items to products.
When the join columns have the same name on both sides, USING (order_id) is a shorter ON. A table can also be joined to itself (a self join) with two different aliases, for example to pair customers from the same city. CROSS JOIN returns every combination of rows with no condition; useful for generating grids, alarming when it happens by accident.
Older code writes joins as FROM a, b WHERE a.id = b.a_id. It is equivalent, but the explicit JOIN ... ON keeps join conditions apart from filters and makes a forgotten condition obvious.
Example
SELECT c.name, o.id AS order_id, p.name AS product, oi.quantity
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.id
JOIN order_items AS oi ON oi.order_id = o.id
JOIN products AS p ON p.id = oi.product_id
WHERE o.status = 'shipped'
ORDER BY o.id, p.name;
-- name | order_id | product | quantity
-- Ana | 101 | Keyboard | 1
-- Ana | 101 | Mouse | 1
-- Ben | 103 | Ebook | 1
-- Ben | 105 | Keyboard | 1
-- Ben | 105 | Monitor | 1
-- Self join: pairs of customers in the same city
SELECT a.name, b.name
FROM customers a
JOIN customers b ON b.city = a.city AND a.id < b.id;
-- Ben | ChloeRemember
- JOIN ... ON keeps pairs of rows where the condition is true
- Alias tables and qualify every column
- Joins multiply rows: one-to-many repeats the 'one' side
- Prefer explicit JOIN syntax over comma joins
Watch out for
- Joining two unrelated one-to-many tables to the same parent and summing, which inflates totals.