Play free SQL arcade Daily trap SQL traps Blog Teachers Full game For teachers · 50 minutes
← Ready-to-teach lessons
JOIN and LEFT JOIN lesson
Joining two tables and finding what's missing in 50 minutes, closing with a real detective case. With a printable worksheet.
Goal
Every student can join two tables on their key, knows which rows JOIN drops and finds the unmatched ones with LEFT JOIN.
Lesson plan
0–5 min Introduce the topic on the board with one example query.
5–20 min Arcade , in pairs or alone. Arcade world 3: The Bridge, On Foot, The Garage and the boss, The Ghost Car.
20–35 min Worksheet : six questions on the same tables.
35–45 min Case: Case 01, "The File Nobody Asked For": two tables and a time window. It's the natural way to close the topic.
45–50 min Class discussion with the questions below. Whoever beat the boss hands in their certificate code.
Materials
Mistakes you'll see
Ambiguous column. Both tables have city: say which one with an alias.A WHERE that cancels the LEFT JOIN. Filtering on a right-hand column turns the LEFT JOIN into a plain JOIN. see the explanation → COUNT(*) with LEFT JOIN. It counts the row of NULLs and gives 1 instead of 0: count a column from the other table.The JOIN that multiplies rows. If one side has several rows per key, sums get inflated. see the explanation → For the class discussion
Why does JOIN return 8 rows when there are 9 thefts? If you swap the tables in a LEFT JOIN, what changes? What question does WHERE t.id IS NULL answer after a LEFT JOIN? Evidence
When a student beats a boss, the arcade gives a certificate with a code. Ask for it as the submission and check it here: check codes →
Student worksheet
Ready to print, no answers.
Name: ______________________________ Date: ____________
JOIN and LEFT JOIN lesson
suspects id name age city job 1 Lucía Prado 34 Lumen Bay photographer 2 Joel Ibarra 52 Salt Harbor locksmith 3 Marta Solís 27 Lumen Bay nurse 4 Iker Vela 41 San Ciro taxi driver 5 Rocío Andrade 63 Salt Harbor jeweler 6 Damián Cuevas 19 San Ciro courier 7 Elena Ruiz 46 Lumen Bay accountant 8 Bruno Paz 38 Salt Harbor mechanic
thefts id suspect_id city loot 1 2 Salt Harbor 1200 2 2 Salt Harbor 800 3 4 San Ciro 300 4 5 Salt Harbor 5000 5 8 Salt Harbor 450 6 8 Lumen Bay 950 7 8 San Ciro 150 8 1 Lumen Bay 2200 9 NULL Lumen Bay 700
The suspect's name and the loot of each theft.
The name and loot of the thefts committed in Salt Harbor.
The total loot of each suspect with thefts.
All suspects with the loot of their thefts, including those with none.
The name of the suspects with no thefts.
How many thefts each suspect has, with 0 for those with none.
casoabiertogame.com · SQL arcade and detective cases, free for the classroom
Want the answer key?
The worksheet answers and the solutions to all 28 cases aren't published, so students won't find them. Write to us from your institutional email and we'll send them, at no cost.
Request the answer key
The arcade, the lessons and the first two cases are free and need no account. For your group to have all 28 cases, the classroom license is free too: how to request it →