The shop database has customers (id, name) and orders (id, customer_id, order_date). Every order's customer_id points at one customer's id. Eight customers, ten orders, and one customer, Hannah Wolf, has never ordered. You can run every query in the SQL sandbox with the shop dataset.
The shop also has products (id, name, category, price) and order_items (order_id, product_id, quantity): one row for each product in an order, 15 rows in all. The exercise has four tasks and takes about 25 minutes.
How a join works
A join puts rows of two tables side by side wherever the ON condition is true. Here are two customers and their orders:
customers.id | customers.name |
|---|---|
1 | Anna Berger |
8 | Hannah Wolf |
orders.id | orders.customer_id |
|---|---|
1 | 1 |
3 | 1 |
10 | 1 |
JOIN ... ON orders.customer_id = customers.id pairs each order with the customer whose id matches. Anna gets three rows, one per order. Hannah has no matching order:
INNER JOIN (or just JOIN) returns only the pairs that match: Anna three times, Hannah not at all.
LEFT JOIN returns every row of the left table (the one named first) and fills the missing side with NULL: Anna three times, plus one row for Hannah with an empty order id.
Common mistakes
Joining on the wrong columns.
ON customers.id = orders.idcompares a customer number with an order number. It runs, but the result is nonsense. Link the foreign key to the key it points at.Forgetting ON. In SQLite a JOIN without ON pairs every row with every row. Some other database systems, such as PostgreSQL, refuse to run it.
Filtering away the LEFT JOIN. A WHERE condition on a column of the right table, such as
WHERE orders.status = 'shipped', removes the NULL rows again, so customers without orders disappear.Ambiguous names. Both tables have a column called
id. Writecustomers.idororders.id, never justid.
For fast finishers
In the sandbox (Office supplies shop):
List every order item with the order date and the product name. You need three tables.
Which customers bought something from the Furniture category? Each name once.
Extension solutions
SELECT orders.order_date, products.name, order_items.quantity
FROM order_items
JOIN orders ON orders.id = order_items.order_id
JOIN products ON products.id = order_items.product_id;15 rows, one per order item.
SELECT DISTINCT customers.name
FROM customers
JOIN orders ON orders.customer_id = customers.id
JOIN order_items ON order_items.order_id = orders.id
JOIN products ON products.id = order_items.product_id
WHERE products.category = 'Furniture';Ben Okafor, Anna Berger and Farid Haddad.
Teacher notes
How to run it. Draw the two small tables of the worked example on the board and let learners pair the rows by hand before they start. Then they work alone; with a class link (unfold "Use it with a class" below the exercise) you collect a result code per learner and see the class average per task in Class results. The SQL joins lesson is a full 90-minute plan around it.
Typical mistakes. Learners expect one row per customer from an inner join; they expect 0 from COUNT(*) for Hannah; in the sort task, "show the customers who have ordered" is put under left join because it mentions customers.
When learners check their answers in the sandbox. "Check my query" compares values column by column, so the columns must come in the same order as the task's solution. For "Orders with names" that is id, name, date. If the check says "some values differ" although the result looks right, press Solution and compare the column order.
Follow-up questions.
Which table do you name first in a LEFT JOIN, and why does it matter?
How many rows would a cross join of products and customers return? (6 × 8 = 48)
Why does a database store customer_id in orders instead of the customer's name?
Differentiation. Easier: do the first quiz questions together with the sandbox on the projector. Harder: ask learners to write the query for "every product with its units sold" and explain why its SUM is empty (NULL) for a product that was never sold.
