SQL JOIN across two tables
Name:
Date:
Class:
A
Quick Questions
Shade in the A or B circle for each question.
1. Which query joins students to their classes by matching the foreign key to the primary key?
2. What does the ON part of a JOIN do?
3. Why do we write customers.name instead of just name in a JOIN?
4. A books table has the foreign key author_id. Which ON condition is correct?
5. What does INNER JOIN return?
B
Match the Pairs
Draw a line connecting each item on the left to its match on the right.
| Left column | Right column | |
|---|---|---|
| 1INNER JOIN Teams ON Players.team_id = Teams.team_id | AThe matching condition: the foreign key in Players must equal the primary key in Teams | |
| 2Players.team_id = Teams.team_id | BOrders.customer_id = Customers.customer_id | |
| 3Show student names with the name of their class by joining two tables on a matching key | CThe 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. | |
| 4Write table.field names so SQL knows which table a field belongs to when both tables share the same field name | DThe ON condition is missing, so SQL does not know which fields to match. It should end with ON Students.class_id = Classes.class_id. | |
| 5SELECT * FROM Students INNER JOIN Classes; | ESELECT Students.name, Classes.class_name FROM Students INNER JOIN Classes ON Students.class_id = Classes.class_id; | |
| 6SELECT * FROM Students INNER JOIN Classes ON class_id = class_id; | FCombines each player row with the team row that has a matching team_id, so both sets of data appear together |
C
Definitions
INNER JOIN t2 ON t1.key = t2.key
D
Fill in the Blanks
Complete each line of code by filling in the missing parts.
1.
SELECT pet.name, owner.name
FROM pet
__________ owner ON pet.ownerid = owner.id;
2.
SELECT pet.name, owner.name
FROM pet
INNER JOIN owner __ pet.ownerid = owner.id;
3.
SELECT pet.name, owner.name
FROM pet
INNER JOIN owner ON ___________ = owner.id;
4.
SELECT pet.name, owner.name
FROM pet
INNER JOIN owner ON pet.ownerid _ owner.id;
5.
SELECT pet.name, __________
FROM pet
INNER JOIN owner ON pet.ownerid = owner.id;
E
Spot the Error
Shade in the letter of the line that contains an error.
Question 1
A
SELECT pet.name, name
B
FROM pet
C
INNER JOIN owner
D
ON pet.owner_id = owner.id
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;
Question 3
A
SELECT film.title,
B
genre.name,
C
FROM film
D
INNER JOIN genre
E
ON film.genre_id = genre.id
Question 4
A
SELECT pupil.name, club.title
B
FROM pupil
C
INNER JOIN pupil
D
ON pupil.club_id = 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;
Answer Key
SQL JOIN across two tables
Section A: Quick Questions
Q1
A — SELECT * FROM students INNER JOIN classes ON students.class_id = classes.class_id;
Q2
A — It says which fields to match between the two tables
Q3
A — To show which table the field belongs to
Q4
B — ON books.author_id = authors.author_id
Q5
B — Only rows where the keys match in both tables
Section B: Match the Pairs
Q1
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
Q2
Players.team_id = Teams.team_id → The matching condition: the foreign key in Players must equal the primary key in Teams
Q3
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;
Q4
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
Q5
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.
Q6
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.
Section C: Definitions
Q1
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.Section D: Fill in the Blanks
Q1
SELECT pet.name, owner.name
FROM pet
INNER JOIN owner ON pet.ownerid = owner.id;Q2
SELECT pet.name, owner.name
FROM pet
INNER JOIN owner ON pet.ownerid = owner.id;Q3
SELECT pet.name, owner.name
FROM pet
INNER JOIN owner ON pet.ownerid = owner.id;Q4
SELECT pet.name, owner.name
FROM pet
INNER JOIN owner ON pet.ownerid = owner.id;Q5
SELECT pet.name, owner.name
FROM pet
INNER JOIN owner ON pet.ownerid = owner.id;Section E: Spot the Error
Q1
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.
Q2
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.
Q3
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.
Q4
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.
Q5
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.