← VCE Spreadsheets, SQL & Visualisation

SQL JOIN across two tables

Combines rows from two tables where a foreign key matches a primary key, so related data shows in one result. Use table.field names to say which table each field belongs to.

Part of: VCE Applied Computing - U1O1 - Data analysis — Module 56, VCE Data Analytics - U3O1 - Data Analytics — Module 27, VCE Data Analytics - U3O2 - Analysis and Design — Module 43, 9-10 Digital Technologies: Acquiring, storing and validating data — Module 28, ACARA Digital Technologies Years 9–10 — Entities, Relationships and Queries — Module 38

Understanding SQL JOIN across two tables in VCE Spreadsheets, SQL & Visualisation

What it does: The query joins Students to Classes using class_id, so each student name appears beside the class_name from the other table. Both tables have a customer_id field, so the table name before the dot makes it clear which one is meant. INNER JOIN needs an ON condition that matches a foreign key to a primary key, written as table.field = table.field. ON gives the matching rule, such as orders.customer_id = customers.customer_id. ORDER BY is what sorts rows. A JOIN does not change the tables. It combines related records into a single result you can read. The ON keyword is required before the matching condition. Without it the query will not run.

Common mistakes: Both tables have a field called name, so a plain name is ambiguous. Use table.field names to say which table each field comes from: SELECT pet.name, owner.name. The ON line must match the foreign key to the primary key of the other table. team.city is not the key. It should be ON player.team_id = team.id.

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 SQL JOIN across two tables to a class →

This module's key terms are also in a printable crossword (Modules 55–70) and word search (Modules 55–86).

Take this worksheet further — free with Cloodit

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

Learning
Duo
Which query joins students to their classes by matching the foreign key to the primary key?
SELECT * FROM students INNER JOIN classes ON students.class_id = classes.class_id;SELECT * FROM students INNER JOIN classes WHERE students.class_id;
Matches
SELECT * FROM Students INNER JOIN Classes;
↓
The ON condition is missing, so SQL does not know which fields to match. It should end with ON Students.class_id = Classes.class_id.
Applying
Meanings
Term
INNER JOIN t2 ON t1.key = t2.key
Meaning
Combines rows from two tables where a foreign key matches a primary key, so related data shows in one result. Use table.field names to say which table each field belongs to.
Practice
SELECT students.name, classes.class_name
Testing
Gaps
owner ON pet.ownerid = owner.id;
Check Answer
Spots
1SELECT pet.name, name
2FROM pet

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

The 'Narwhal' badge — Unicorn Coder, 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. Which query joins students to their classes by matching the foreign key to the primary key?
A. SELECT * FROM students INNER JOIN classes ON students.class_id = classes.class_id; ✓
B. SELECT * FROM students INNER JOIN classes WHERE students.class_id;
INNER JOIN needs an ON condition that matches a foreign key to a primary key, written as table.field = table.field.
2. What does the ON part of a JOIN do?
A. It says which fields to match between the two tables ✓
B. It sorts the rows from the two tables
ON gives the matching rule, such as orders.customer_id = customers.customer_id. ORDER BY is what sorts rows.
3. Why do we write customers.name instead of just name in a JOIN?
A. To show which table the field belongs to ✓
B. To make the query run faster
table.field names remove confusion when both tables have fields with the same name, such as name or id.
4. A books table has the foreign key author_id. Which ON condition is correct?
A. ON books.title = authors.author_id
B. ON books.author_id = authors.author_id ✓
The foreign key in one table must be matched to the primary key in the other. A title is not a key.
5. What does INNER JOIN return?
A. Every row from both tables, matched or not
B. Only rows where the keys match in both tables ✓
INNER JOIN keeps only the rows that have a match in both tables. Unmatched rows are left out.

Match the Pairs

INNER JOIN Teams ON Players.team_id = Teams.team_id → Combines each player row with the team row that has a matching team_id, so both sets of data appear together
Players.team_id = Teams.team_id → The matching condition: the foreign key in Players must equal the primary key in Teams
Show student names with the name of their class by joining two tables on a matching key → SELECT Students.name, Classes.class_name FROM Students INNER JOIN Classes ON Students.class_id = Classes.class_id;
Write table.field names so SQL knows which table a field belongs to when both tables share the same field name → Orders.customer_id = Customers.customer_id
SELECT * FROM Students INNER JOIN Classes; → The ON condition is missing, so SQL does not know which fields to match. It should end with ON Students.class_id = Classes.class_id.
SELECT * FROM Students INNER JOIN Classes ON class_id = class_id; → The field names have no table names in front, so it is unclear which class_id belongs to which table. Use Students.class_id = Classes.class_id.

Key Term

INNER JOIN t2 ON t1.key = t2.key
Combines rows from two tables where a foreign key matches a primary key, so related data shows in one result. Use table.field names to say which table each field belongs to.

Fill in the Blanks

1. SELECT pet.name, owner.name FROM pet INNER JOIN owner ON pet.ownerid = owner.id;
Hint: Type the keyword pair that combines matching rows from two tables.
2. SELECT pet.name, owner.name FROM pet INNER JOIN owner ON pet.ownerid = owner.id;
Hint: Type the keyword that introduces the matching condition for the join.
3. SELECT pet.name, owner.name FROM pet INNER JOIN owner ON pet.ownerid = owner.id;
Hint: Type the foreign key from the pet table, written as table.field.
4. SELECT pet.name, owner.name FROM pet INNER JOIN owner ON pet.ownerid = owner.id;
Hint: Type the operator that checks the foreign key matches the primary key.
5. SELECT pet.name, owner.name FROM pet INNER JOIN owner ON pet.ownerid = owner.id;
Hint: Type the owner's name field, using the table name to show which table it belongs to.

Spot the Error

Question 1
A SELECT pet.name, name
B FROM pet
C INNER JOIN owner
D ON pet.owner_id = owner.id
Line A — Both tables have a field called name, so a plain name is ambiguous. Use table.field names to say which table each field comes from: SELECT pet.name, owner.name.
Question 2
A SELECT player.name, team.city
B FROM player
C INNER JOIN team
D ON player.team_id = team.city
E ORDER BY player.name;
Line D — The ON line must match the foreign key to the primary key of the other table. team.city is not the key. It should be ON player.team_id = team.id.
Question 3
A SELECT film.title,
B genre.name,
C FROM film
D INNER JOIN genre
E ON film.genre_id = genre.id
Line B — There is a comma after the last field in the SELECT list, so SQL expects another field before FROM. The last field must have no comma: genre.name.
Question 4
A SELECT pupil.name, club.title
B FROM pupil
C INNER JOIN pupil
D ON pupil.club_id = club.id
Line C — pupil is already in the FROM line, so this joins the table to itself. INNER JOIN must name the second table, club, so the query can use club.title and club.id.
Question 5
A SELECT book.title, author.name
B FROM book
C INNER JOIN author
D ON book.author_id == author.id
E WHERE author.name = 'Ng';
F ORDER BY book.title;
Line D — SQL uses a single = to compare values. The double == is not valid in standard SQL. It should be ON book.author_id = author.id.

Student with a class code? Join your class →