Computing · Secondary 4

Active learning ideas

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.

• MOE Syllabus Outcomes
• MOE: Data Management - S4
• MOE: SQL Programming - S4

• 25–40 min
• Pairs → Whole Class
• 4 activities

Activity 01

• 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.

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.

What to look for

Present students with two simple tables (e.g., 'Students' and 'Courses') and a common ID column. Ask them to write an INNER JOIN query to list students and the courses they are enrolled in. Review their queries for correct syntax and logic.

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

Activity 02

• 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.

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.

What to look for

Provide a scenario: 'A school wants to find all students who have not yet submitted their project, even if they are enrolled in a class.' Ask students to discuss which type of join (INNER or LEFT) would be most appropriate and why, guiding them to consider the 'left' and 'right' tables in this context.

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

Activity 03

• 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.

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.

What to look for

Give students a database schema showing three related tables (e.g., 'Customers', 'Orders', 'Products'). Ask them to write a SQL query using at least two joins to retrieve the customer name, order date, and product name for all orders placed.

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

Activity 04

• 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.

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.

What to look for

Present students with two simple tables (e.g., 'Students' and 'Courses') and a common ID column. Ask them to write an INNER JOIN query to list students and the courses they are enrolled in. Review their queries for correct syntax and logic.

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

A few notes on teaching this unit

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.

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

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

  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.

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

  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.

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

  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.

---

Methods used in this brief

• Problem-Based Learning