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
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
Pattern matching with LIKE
Grouping with GROUP BY
HAVING, not WHERE
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 mark-by-mark plan — for / against / judgement, with marking guidance — in study mode.