Joining tables in SQL

What INNER JOIN and LEFT JOIN return, worked through on a small shop database. Match the parts of a join, predict how many rows come back, choose the right join for seven questions and complete eight queries.

Exercise25 minBachelor +2CC BY 4.0
4 tasks · 25 min

What you will learn

  • Explain what a join does with two tables
  • Tell INNER JOIN and LEFT JOIN apart by their result
  • Write the ON condition that links two tables
  • Predict the number of rows a join returns

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.id compares 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. Write customers.id or orders.id, never just id.

For fast finishers

In the sandbox (Office supplies shop):

  1. List every order item with the order date and the product name. You need three tables.

  2. Which customers bought something from the Furniture category? Each name once.

Extension solutions
Task 1
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.

Task 2
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.

Use it with a class
  1. 1. Copy the class link and share it with your class.
  2. 2. Learners finish the exercise and get a result code.
  3. 3. Paste the codes into Class results to see everyone at once.
Change the tasks in the builder

Reuse this material

CC BY 4.0

You may copy, adapt and share it, also commercially, as long as you credit ManyPI and link back.

"Joining tables in SQL" by ManyPI (https://manypi.com/edu/sql-joins-exercise), licensed under CC BY 4.0 (https://creativecommons.org/licenses/by/4.0/)