Data lives in separate tables
A database does not store an order as "Ana bought a book". It stores customers in one table and orders in another, and each order carries the id of its customer. That way Ana's name is written once, however many orders she has.
To get "Ana bought a book" back, the two tables have to be put together. That is a join: for each row on one side, find the matching rows on the other.
Every join answers the same two questions:
- Which rows match? Usually given by
ON orders.customer_id = customers.id. - What happens to rows that match nothing? This is the only difference between the join types.
Two tables, five joins
The tables below are set up to have one of each kind of row. Cy is a customer with no orders. The gift order has no customer. Ana has two orders.
Switch between the joins and watch which rows are crossed out.
customers
orders
SELECT c.name, o.item FROM customers c INNER JOIN orders o ON o.customer_id = c.id
Result
3 rows. Only pairs that match. A row with no partner on the other side is dropped.
INNER JOIN: only the matches
Keep a pair only when both sides exist. Cy disappears, because he has no order. The gift disappears, because it has no customer.
This is the default: writing just JOIN means INNER JOIN.
LEFT JOIN: keep everything on the left
Every row of the first table survives. Where there is no match, the columns from the second table are filled with NULL.
Cy is back, with NULL for the item. Use this for "all customers, and their orders if they have any".
Example
Finding rows with no match
A left join is also how you find what is missing. Customers who have never ordered:
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;The join keeps Cy with NULLs in the order columns, and the WHERE keeps only such rows.
RIGHT JOIN: keep everything on the right
The mirror image. Every order survives, and the gift order appears with NULL for the customer.
Any right join can be rewritten as a left join by swapping the two tables. Most people only ever write left joins, so that "the table I care about" always comes first.
FULL JOIN: keep everything
Matched pairs, plus the leftovers from both sides. Cy appears with no item and the gift appears with no customer. Useful for comparing two lists to see what is in one, the other, or both.
CROSS JOIN: every combination
No matching condition at all. Each row on the left is paired with every row on the right: 3 customers × 4 orders = 12 rows.
It is occasionally what you want, for example every size combined with every colour. More often it appears by accident, when a join condition is forgotten, and a query that should return a thousand rows returns a billion.
Self join: a table joined to itself
The sixth kind is not a new rule. It is any of the joins above, with the same table on both sides. It is used when rows refer to other rows in the same table.
SELECT worker.name, boss.name AS manager
FROM employees worker
LEFT JOIN employees boss ON boss.id = worker.manager_id;The table is given two different names so the query can tell the two roles apart. The left join keeps the person at the top, who has no manager.
Side by side
| Join | Rows with no match on the left | Rows with no match on the right | Result here |
|---|---|---|---|
| INNER | Dropped | Dropped | 3 rows |
| LEFT | Kept, with NULLs | Dropped | 4 rows |
| RIGHT | Dropped | Kept, with NULLs | 4 rows |
| FULL | Kept, with NULLs | Kept, with NULLs | 5 rows |
| CROSS | No matching: every pairing | 12 rows |
Key takeaways
- A join puts rows from two tables together where a condition matches.
- The join types differ only in what happens to rows that match nothing.
- INNER drops them. LEFT keeps the left table's. RIGHT keeps the right table's. FULL keeps both.
- CROSS pairs every row with every row and has no condition.
- A self join is a table joined to itself under two names.