Keys identify rows; the join type decides what happens to rows that do not match. Run all four joins on two live tables, then break a primary key and watch the row count multiply.
Keys: how a row says who it is
Primary key
customers.id is unique per row and never NULL. It is the row's identity: "customer 1" means exactly one person. The orders table has its own primary key, order_id.
Foreign key
orders.customer_id stores another table's primary key to point at it. Each order carries the id of the customer who placed it. That pointer is the thing a join follows.
One pointer below is broken on purpose: order 106 references customer 6, and no such customer exists (imagine the customer was deleted without cleaning up their orders). Databases call this an orphan row; a foreign key constraint would have rejected it. Watch what the outer joins do with it.
The join lab
Pick a join type and the query below actually executes on these two tables. Key columns are color coded by value so you can watch rows find their partners. The fate column tells each input row what the current join will do with it, and every row count is computed from the executed result.
Keeps only the rows whose key matches on both sides. Unmatched rows from either table are dropped.
SELECT c.id, c.name, c.city, o.order_id, o.item, o.amount FROM customers c INNER JOIN orders o ON o.customer_id = c.id;
customers
5 rows| id (PK) | name | city | fate |
|---|---|---|---|
| 1 | Alice | Oslo | joins |
| 2 | Bruno | Bergen | joins |
| 3 | Chen | Drammen | dropped |
| 4 | Dana | Stavanger | joins |
| 5 | Elin | Trondheim | dropped |
orders
6 rows| order_id (PK) | customer_id (FK) | item | fate |
|---|---|---|---|
| 101 | 1 | Keyboard | joins |
| 102 | 1 | Monitor | joins |
| 103 | 2 | Desk mat | joins |
| 104 | 4 | Laptop stand | joins |
| 105 | 4 | USB hub | joins |
| 106 | 6 | Webcam | dropped |
customers in
5
orders in
6
result rows out
5
5 matched pairs = 5 rows.
result: customers INNER JOIN orders
5 rows| id | name | city | order_id | item | amount |
|---|---|---|---|---|---|
| 1 | Alice | Oslo | 101 | Keyboard | $120 |
| 1 | Alice | Oslo | 102 | Monitor | $310 |
| 2 | Bruno | Bergen | 103 | Desk mat | $25 |
| 4 | Dana | Stavanger | 104 | Laptop stand | $85 |
| 4 | Dana | Stavanger | 105 | USB hub | $45 |
Break the key: the fan-out hazard
A join key only works because the primary key side is unique. Break that promise and the arithmetic changes: for each key value, the join produces every combination of matching rows, so the row count for that key is left-side count times right-side count. Add copies of Alice's row (customer id 1) and watch the inner join below re-execute.
customers
5 rows| 1 | Alice | Oslo | |
| 2 | Bruno | Bergen | |
| 3 | Chen | Drammen | |
| 4 | Dana | Stavanger | |
| 5 | Elin | Trondheim |
The multiplication, computed
1 customer row with id 1 × 2 orders with id 1 = 2 joined rows for key 1
Inner join result
5 rows
was 5 with the key intact
Rows for key 1
2
was 2 with the key intact
This is fan-out. In production it happens when you join on a column you assumed was unique (an email, a date, a product code) and it is not. Every duplicate multiplies, and any sum or average computed downstream silently inflates. The first thing to check after any join is the row count.
Mini challenge
The Question
Which customers have no orders?
You are joining customers to orders and will filter afterwards. Which join type guarantees the answer is even in the result?
Pick a join type to get computed feedback from the actual join results.