Use LEFT JOIN in H2 Computing 9569 practice when the task requires every row from your starting table, including rows without a match. If the task asks only for matched records, an INNER JOIN can be appropriate. The official reference guide linked for the 2027 A-Level syllabus shows both forms. Decide which rows should survive before looking up the syntax.
The easiest test is to make a tiny dataset with one deliberately unmatched record. Predict the result before running your query. If the unmatched record disappears when the task asks for every record, check the join type and any filters you added afterwards.
- Identify the table whose rows the task wants to keep.
- Write down what should happen when there is no match.
- Check the join condition against the keys in your schema.
- Test the query with a matched row and an unmatched row.
CHAPTER 1 OF 4 · Back to contents
Start with the wording of the task
“List all clubs and show their meeting rooms, where assigned” starts with clubs. A club can still belong in the result when it has no room. “List clubs with a room assignment” describes a smaller result. Both tasks can use the same two tables, but they use different rules about which clubs appear.
Draw the two tables on paper and circle the column that connects them. If the answer needs every club, put Clubs on the left of your LEFT JOIN. Select the fields requested and check how the question wants the result ordered.
CHAPTER 2 OF 4 · Back to contents
Worked example | A club has no room yet
Suppose Clubs contains Debate, Robotics and Art. RoomAssignments has a row for Debate and a row for Art, but no row for Robotics. An INNER JOIN on the club identifier returns the two clubs with assignments. A LEFT JOIN starting with Clubs returns all three and shows a missing room value for Robotics.
Assume Clubs has club_id and name, and RoomAssignments has club_id and room. Try this query with the example data.
SELECT c.name, r.room
FROM Clubs AS c
LEFT JOIN RoomAssignments AS r ON c.club_id = r.club_id
ORDER BY c.name;Now test what happens when you add a filter that keeps only rows with a particular room number. A condition on the room column in WHERE can remove the unmatched rows after the join. If the task still expects every club, check whether the room restriction belongs in the join condition or whether you need to change how you display the result. Read the task before choosing.
Contents · Previous chapter · Next chapter
CHAPTER 3 OF 4 · Back to contents
Check the result, not just the query
Count the rows you expected from your test data and compare them with the actual output. If you have more rows than expected, see whether a club has several assignments or whether you joined on a field that is not unique. An apparently correct first row can hide a join that duplicates later records.
Practise writing the expected output before running the SQL. Then use the reference guide to check the syntax and return to your small test. This makes the guide useful without spending time searching it for the logic of the answer.
Contents · Previous chapter · Next chapter
CHAPTER 4 OF 4 · Back to contents
Frequently asked questions
Does LEFT JOIN always return exactly one row per club?
A club with several matching rows can appear several times. Check the task and table relationship before deciding whether that is appropriate.
Where is the reference material?
Find the official 9569 reference guide and specimen papers in the 2027 SEAB A-Level school-candidate directory.