SQL: Joining Tables

Active learning helps students grasp SQL joins because these concepts rest on visualizing relationships between tables. When students write and run queries themselves, they see how data links in real time, which builds durable understanding. Hands-on work also reveals the impact of join types on results, making abstract ideas concrete through direct experience.

• Secondary 4
• Computing
• 4 activities
• 25 min–40 min

Learning Objectives

1. Compare the results of INNER JOIN and LEFT JOIN queries on a given dataset, explaining the differences in output.
2. Design a SQL query that retrieves specific data by joining at least three related tables.
3. Analyze a database schema to identify appropriate foreign keys for joining tables.
4. Explain the logical process of combining records from two tables based on a common attribute.

Problem-Based Learning

⏱ 30 min·Pairs

Pair Practice: INNER vs LEFT JOIN

Provide pairs with two tables, such as customers and orders. Students write and run INNER JOIN and LEFT JOIN queries in an online SQL editor. They discuss and note differences in result sets, then predict outcomes before executing.

Prepare & details

What is the logic behind combining data from multiple tables using JOINs?

Facilitation Tip: During Pair Practice, have students run both INNER and LEFT JOIN queries on the same dataset and annotate the result sets side by side.

Setup: Groups at tables with access to research materials

Materials: Problem scenario document, KWL chart or inquiry framework, Resource library, Solution presentation template

• Analyze
• Evaluate
• Create
• Decision-Making
• Self-Management
• Relationship Skills

Problem-Based Learning

⏱ 40 min·Small Groups

Small Group Challenge: Three-Table Joins

Groups receive tables for books, authors, and publishers. They design a query joining all three to list books with author details. Groups test queries, refine for accuracy, and share one insight with the class.

Prepare & details

Differentiate between INNER JOIN and LEFT JOIN and their respective use cases.

Facilitation Tip: For the Small Group Challenge, assign each group a unique schema to encourage varied solutions and peer comparison.

Setup: Groups at tables with access to research materials

Materials: Problem scenario document, KWL chart or inquiry framework, Resource library, Solution presentation template

• Analyze
• Evaluate
• Create
• Decision-Making
• Self-Management
• Relationship Skills

Problem-Based Learning

⏱ 25 min·Individual

Individual Debug: Faulty Joins

Students get five buggy multi-table queries. Individually, they identify errors like missing conditions or wrong join types, correct them, and verify results. Follow with pair sharing of fixes.

Prepare & details

Design a SQL query that retrieves information by joining three or more tables.

Facilitation Tip: In Faulty Joins, provide queries with missing or misplaced join conditions so students practice debugging with real errors.

Setup: Groups at tables with access to research materials

Materials: Problem scenario document, KWL chart or inquiry framework, Resource library, Solution presentation template

• Analyze
• Evaluate
• Create
• Decision-Making
• Self-Management
• Relationship Skills

Problem-Based Learning

⏱ 35 min·Whole Class

Whole Class: School Database Scenario

Display a school database schema. As a class, brainstorm and vote on a multi-table query, such as student grades with teachers and subjects. Execute live and analyze results together.

Prepare & details

What is the logic behind combining data from multiple tables using JOINs?

Facilitation Tip: In the School Database Scenario, ask students to sketch the expected output before writing code to reinforce prediction skills.

Setup: Groups at tables with access to research materials

Materials: Problem scenario document, KWL chart or inquiry framework, Resource library, Solution presentation template

• Analyze
• Evaluate
• Create
• Decision-Making
• Self-Management
• Relationship Skills

Teaching This Topic

Teach SQL joins by moving from concrete examples to abstract rules. Start with simple two-table queries, then introduce three-table examples only after students can articulate why a join is needed. Avoid lecturing on join theory first. Instead, let students encounter mismatches firsthand, then formalize the rules afterward. Research shows this inductive approach strengthens retention because students build mental models from observed outcomes.

What to Expect

Students will confidently choose the correct join for a given task and explain why their query returns the expected rows. They will also recognize mismatches between inner and left joins by comparing outputs, and construct multi-table queries without omitting key conditions. Success looks like accurate, executable queries and clear reasoning about table relationships.

Watch Out for These Misconceptions

Common Misconception

During Pair Practice: 'INNER JOIN and LEFT JOIN always return the same number of rows.'

What to Teach Instead

During Pair Practice, have partners run both queries and compare record counts. When outputs differ, ask them to identify which rows are missing in the INNER JOIN and why. Guide them to note that LEFT JOIN includes unmatched left-table rows, which INNER JOIN excludes.

Common Misconception

During Small Group Challenge: 'Omitting join conditions creates useful full combinations.'

What to Teach Instead

During Small Group Challenge, provide a query without conditions and ask groups to run it. When they see thousands of rows, have them add a join condition and compare outputs. Discuss why Cartesian products are rarely useful and how keys prevent them.

Common Misconception

During Pair Practice: 'Table order in JOIN does not matter.'

What to Teach Instead

During Pair Practice, ask students to swap the order of tables in a LEFT JOIN query. Have them run both versions and compare results. Prompt a discussion on why the left table determines included rows and how this affects query output.

Assessment Ideas

Quick Check

After Pair Practice, give students two simple tables with a common ID. Ask them to write an INNER JOIN query to list matching rows, then swap to a LEFT JOIN and explain the difference in outputs.

Discussion Prompt

During the Small Group Challenge, circulate and ask groups to justify their chosen join type for a scenario like finding students without project submissions. Listen for references to which table should be the 'left' table and why.

Exit Ticket

After Faulty Joins, provide a schema with three tables and a faulty query. Ask students to correct the query and explain how their fix ensures accurate results, demonstrating understanding of join conditions and table relationships.

Extensions & Scaffolding

• Challenge early finishers to write a query that joins four tables, filtering for students with at least two enrollments in advanced subjects.
• For students who struggle, provide a partially completed query with missing join conditions and ask them to fill in keys and test outputs.
• As extra time permits, introduce FULL OUTER JOIN and have students compare its behavior to INNER and LEFT JOIN using a dataset with unmatched records in both tables.

Key Vocabulary

JOIN	A clause used in SQL to combine rows from two or more tables based on a related column between them.
INNER JOIN	Returns only the rows where the join condition is met in both tables being joined.
LEFT JOIN	Returns all rows from the left table and the matched rows from the right table; if no match, NULL values are returned for the right table's columns.
Foreign Key	A column in one table that uniquely identifies a row of another table or the same table, used to establish a link between tables.