← ACARA Digital Technologies Years 9–10

Why a query over two tables needs a join

A join matches each foreign key value in one table to the primary key value in another, so rows from both tables show together. Rows with no match are left out.

Part of: ACARA Digital Technologies Years 9–10 — Entities, Relationships and Queries — Module 37, QCE Digital Solutions Unit 2 — Data and Programming Techniques — Module 70, QCE Digital Solutions Unit 2 — Prototype Data Solutions — Module 38, QCE Digital Solutions Unit 3 — Interactions Between Users, Data and Digital Systems — Module 110, QCE Digital Solutions Unit 3 — Innovative Digital Solutions — Module 35, QCE Digital Solutions Unit 4 — Prototype Digital Data Exchanges — Module 71, WA Computer Science ATAR Year 11 — Data Management — Module 74

Understanding Why a query over two tables needs a join in ACARA Digital Technologies Years 9–10

What it does: The JOIN pulls the name from customers and the total from orders in one result. The ON condition links the foreign key in orders to the primary key in customers. A join only reads and matches data. It never changes or deletes rows in the tables. Storing the name once avoids repeated data. The foreign key and a join let you bring the name back whenever you need it. The orders table stores the foreign key customer_id, so the name must be looked up in another table.

Common mistakes: The join must bring in the orders table, which holds customer_id and total. Joining customers to itself matches nothing useful. It should be JOIN orders o. The orders table only holds customer_id. The customer's name lives in the customers table, so it must be selected as c.name after the join.

Class: _____________________  ·  Name: _____________________  ·  Date: _________

Free printable worksheet with full answer key — or add this module to a class and your students practise it interactively.

🖨 Print worksheet + answer key Add Why a query over two tables needs a join to a class →

This module's key terms are also in a printable crossword (Modules 459–475) and word search (Modules 459–534).

Take this worksheet further — free with Cloodit

Each concept is reinforced from every angle — learned, then applied, then tested.

Learning
Duo
An orders table stores only customer_id, and the customer's name is in a customers table. How do you show each order with the customer's name?
Select from the orders table only, because the name is stored in itJoin the two tables so each customer_id is matched to a customer
Matches
SELECT name, total FROM orders;
↓
The name field is not in the orders table, which holds only customer_id. The query needs a JOIN to customers.
Applying
Meanings
Term
JOIN
Meaning
A join matches each foreign key value in one table to the primary key value in another, so rows from both tables show together. Rows with no match are left out.
Practice
SELECT students.firstName, orders.orderId, orders.quantity
Testing
Gaps
FROM orders students
Check Answer
Spots
2FROM customers c
3JOIN customers o

Cloodit turns curriculum-aligned coding content into journeys students work through one biome at a time.

The 'Badger' badge — Determined Digger, one of Cloodit's 106 collectible badges
Students earn collectable badges

Over 100 of them, across seven natural biomes.

Study Buddy
Students build a real study resource

Turns their work into revision material ready for Flash Cards and Exam study.

Diamond Dash — one of Cloodit's live, competitive Sprint games
Students lock in with Sprints

Live, competitive practice for the start of the lesson or a teacher-led lesson finisher.

A Cloodit certificate of achievement, ready to personalise and print
Custom printable certificates

Honour your top performers, or encourage a student who's trying hard — ready to print in seconds.

Add to class →

Quick Questions

1. An orders table stores only customer_id, and the customer's name is in a customers table. How do you show each order with the customer's name?
A. Select from the orders table only, because the name is stored in it
B. Join the two tables so each customer_id is matched to a customer ✓
The name lives in the customers table, so a query must join the two tables to bring the name and the order together.
2. Which field acts as the bridge that links an order row to its customer row?
A. The order date in orders
B. The foreign key customer_id in orders ✓
The foreign key in orders holds a customer's primary key value. A join uses this link to match rows.
3. What does a join do with a foreign key value like customer_id = 3 in the orders table?
A. It finds the customer whose primary key is 3 and shows the rows together ✓
B. It deletes the customer whose primary key is 3 from the customers table
A join only reads and matches data. It never changes or deletes rows in the tables.
4. Which query needs a join?
A. List each order with the name of the customer who placed it ✓
B. List every order amount from the orders table
The first query needs data from two tables, order details and customer names. The second only uses one table.
5. An order row has customer_id = 9, but no customer in the customers table has a primary key of 9. What happens in a join?
A. That order row is left out of the results ✓
B. That order row shows up with a random customer name
Rows with no match are left out of a join result. SQL never invents a match.

Match the Pairs

orders(order_id, customer_id, total) → The orders table holds only a customer_id, not the customer's name
customers(customer_id, name, city) → The customer name lives in this table, where customer_id is the primary key
To show each order with the customer's name, join the two tables so matching rows appear together → SELECT customers.name, orders.total FROM orders JOIN customers ON orders.customer_id = customers.customer_id;
The foreign key is the bridge: a join matches it to the primary key in the other table → ON orders.customer_id = customers.customer_id
SELECT name, total FROM orders; → The name field is not in the orders table, which holds only customer_id. The query needs a JOIN to customers.
FROM orders JOIN customers ON orders.order_id = customers.customer_id → The ON condition matches the wrong field. It should match the foreign key orders.customer_id to customers.customer_id.

Key Term

JOIN
A join matches each foreign key value in one table to the primary key value in another, so rows from both tables show together. Rows with no match are left out.

Fill in the Blanks

1. SELECT orders.orderId, students.firstName FROM orders JOIN students ON orders.studentId = students.studentId
Hint: Type the keyword that combines rows from the two tables so the student's name shows beside each order.
2. SELECT orders.orderId, students.firstName FROM orders JOIN students ON orders.studentId = students.studentId
Hint: Type the keyword that introduces the condition used to match rows between the two tables.
3. SELECT orders.orderId, students.firstName FROM orders JOIN students ON orders.studentId = students.studentId
Hint: Type the foreign key column in the orders table, the bridge that links each order to a student.
4. SELECT orders.orderId, students.firstName FROM orders JOIN students ON orders.studentId = students.studentId
Hint: Type the operator that says the foreign key value must match the primary key value.
5. SELECT orders.orderId, students.firstName FROM orders JOIN students ON orders.studentId = students.studentId
Hint: Type the name of the second table, which is where each person's name is stored.

Spot the Error

Question 1
A SELECT c.name, o.total
B FROM customers c
C JOIN customers o
D ON c.id = o.customer_id
Line C — The join must bring in the orders table, which holds customer_id and total. Joining customers to itself matches nothing useful. It should be JOIN orders o.
Question 2
A SELECT o.order_id, o.name
B FROM orders o
C JOIN customers c
D ON o.customer_id = c.id
Line A — The orders table only holds customer_id. The customer's name lives in the customers table, so it must be selected as c.name after the join.
Question 3
A SELECT c.name, o.total
B FROM order o
C JOIN customers c
D ON o.customer_id = c.id
Line B — The table is called orders, not order. A query can only join tables that exist, so the line should be FROM orders o.
Question 4
A SELECT c.name, o.total
B FROM orders c
C JOIN customers c
D ON o.customer_id = c.id
E WHERE o.total > 50
Line B — The alias c is already given to customers on line 3. Using c for orders as well makes the columns unclear. Orders should use the alias o: FROM orders o.
Question 5
A SELECT c.name, o.total
B FROM customers c JOIN orders o
C ON c.id = o.order_id
D WHERE o.total > 50
Line C — A join must match the foreign key to the primary key. o.order_id is not the foreign key, so customers would be matched to the wrong orders. It should be ON c.id = o.customer_id.

Student with a class code? Join your class →