Who made order 105?
A parcel for order 105 comes back to the coffee shop. You need to email the customer.
The orders table says order 105 belongs to customer 1. It holds no name and no email. Those are in a second table, customers. You need both tables to answer one question.
By the end of this lesson
Note
I can join two tables to answer one question.
Key words
| Word | What it means | Example |
|---|---|---|
| Primary key | a column that names each row uniquely | customers.id |
| Foreign key | a column holding another table's primary key | orders.customer_id |
| Join | to put matching rows from two tables side by side | |
| ON | the part of a join that says which columns match | |
| Relationship | how rows in two tables belong together |
The cells students fill in are left blank in this preview.
Keys link the two tables
Each customer has an id that no other customer has. That is the primary key.
Each order stores its customer's id in customer_id. That is a foreign key: it points at a row in another table. So the name is stored once, in customers, and every order points at it.
Try it: follow the key by eye
-- The two tables on their own.
SELECT * FROM customers;
SELECT * FROM orders;
-- Order 105 has customer_id 1. Who is customer 1?
JOIN ... ON matches the rows
JOIN customers brings the second table into the query. ON orders.customer_id = customers.id says which rows belong together.
For each order, the database finds the customer whose id matches. It puts the two rows side by side. Write the table name before each column, such as orders.id, because both tables have an id.
Try it: orders with names
-- Each order beside the name of the customer who made it.
SELECT orders.id, customers.name, orders.total
FROM orders
JOIN customers ON orders.customer_id = customers.id;
-- Add customers.email to the SELECT, then press Run.
Worked example: matching on the key
| orders.id | customer_id | Matching customers row | name |
|---|---|---|---|
| 101 | 1 | id 1 | Amira Shah |
| 102 | 2 | id 2 | Tom Price |
| 103 | 3 | id 3 | Ewa Nowak |
| 105 | 1 | id 1 | Amira Shah |
The cells students fill in are left blank in this preview.
A common mistake
Warning
Many people think the database works out which columns match on its own. It does not. Without ON, it pairs every order with every customer: 6 orders times 4 customers gives 24 rows. Always write the ON condition, and check the row count looks right.
A join works with WHERE and GROUP BY
Once the tables are joined, the rest of SQL works as before.
You can add WHERE customers.city = 'Leeds' to keep only Leeds orders. You can GROUP BY customers.name to total each customer's spending. This is the kind of query an analyst writes many times a day.
Try it: spending per customer
-- How much each customer has spent, biggest first.
SELECT customers.name, COUNT(*) AS orders, SUM(orders.total) AS spent
FROM orders
JOIN customers ON orders.customer_id = customers.id
GROUP BY customers.name
ORDER BY spent DESC;