The big idea: Normalising split the data across tables, so most real questions need two of them at once.
JOIN pairs each row of one table with the matching rows of the other, using the foreign key that links them.
Two tables joined, filtered, narrowed and sorted — one clause at a time. Step through it.
Interactive diagram
Explore the labelled diagram, charts and maps for this topic in full study mode.
Free preview
This is the free notes preview
You're reading the free notes. Aimnova Pro unlocks the full study experience — and you can try it with your first topic free to keep:
- FlashcardsLock in vocabulary and key terms with spaced repetition.
- Practice questionsAnswer exam-style questions and get instant AI marking.
- Mock exams & past-paper vaultSit full mocks and see exactly how examiners award marks.
- Personalised study planA daily plan built around your exam date and weak areas.
Written in one order, applied in another: SELECT is written first but applied near the end.
The database works out which rows first — FROM, JOIN, WHERE — and only then decides which columns to hand back.
Which rows
- FROM — the first table
- JOIN … ON — pair it with the second, on the matching key
- WHERE — keep only rows meeting a condition
- Conditions combine with AND, OR and NOT
Which columns
- SELECT — name the columns wanted
- DISTINCT — remove duplicate rows from the result
- Prefix a column with its table when both tables have it
How it comes back
- ORDER BY — sort the result
- ASC ascending (the default), DESC descending
- Without it, the order is not guaranteed at all
SELECT Student.Name, Membership.Club
FROM Student
JOIN Membership
ON Student.StudentID = Membership.StudentID
WHERE Student.YearGroup = 12
ORDER BY Membership.Club ASC;The ON condition is the whole join: It names the two columns that must match — almost always a foreign key and the primary key it points at.
Leave it out and the database pairs every row with every row, which on two 1,000-row tables gives a million.
Memorize terms 3x faster
Smart flashcards show you cards right before you forget them. Perfect for definitions and key concepts.
Comparing and ranges
Relational operators are the usual ones: equals, not equals, less than, greater than.
BETWEEN covers a range inclusively: BETWEEN 10 AND 12 includes both 10 and 12.
Pattern matching with LIKE
LIKE compares against a pattern, with % standing for any run of characters.
LIKE 'Sm%' finds anything starting Sm; LIKE '%son' anything ending son; LIKE '%ann%' anything containing ann.
Grouping with GROUP BY
GROUP BY collapses rows that share a value into one row each, so totals and counts can be worked out per group.
GROUP BY Club with COUNT gives the number of members in each club.
HAVING, not WHERE
WHERE filters rows before grouping; HAVING filters groups after.
"Clubs with more than five members" needs HAVING, because the count does not exist until the grouping has happened.
SELECT Membership.Club, COUNT(*) AS Members
FROM Student
JOIN Membership
ON Student.StudentID = Membership.StudentID
WHERE Student.Name LIKE 'A%'
GROUP BY Membership.Club
HAVING COUNT(*) > 2
ORDER BY Members DESC;Reading that query: Join the two tables · keep only students whose name starts with A · group what is left by club · keep only clubs with more than two such students · sort by count, largest first.
How this is tested — you must write valid SQL with the clauses in the right order and the join condition correct. It comes up two ways:
Paper 1 Section A
- Construct a query across two tables, 4-6 marks
- Add filtering, pattern matching or ordering
- Say what a given query returns
Paper 1 Section B — case study
- Query the case study's tables
- Explain what a query would tell that organisation
The classic trap: Using WHERE where HAVING is needed. A condition on a count or total can only be applied after GROUP BY — and only HAVING runs there.
Using Student (StudentID, Name, YearGroup) and Membership (StudentID, Club), write a query listing the names of Year 12 students in clubs beginning with D, sorted by name.
Model answer plan
See the answer broken into clear points, with guidance on how each point earns marks.