Fill in the blanksQuestion 1 · Recall
Loading…
By the end you can write a query that uses INNER JOIN to answer a question from two tables,
say which rows a join returns and which it drops, and combine a join with GROUP BY.
Suppose every student row stored the teacher's name:
| StudentID | Name | ClassID | Teacher |
|---|---|---|---|
| 1 | Ada | 1 | Mr Okafor |
| 3 | Cleo | 1 | Mr Okafor |
| 5 | Eve | 1 | Mr Okafor |
"Mr Okafor" is typed three times. Misspell one and the class has two different teachers as far as the database is concerned. If he is replaced, you must update every row — and miss one.
So the teacher is stored once, in a Class table, and each student stores only the
ClassID. That is why a foreign key exists: it guarantees the ClassID means something.
The cost is that no single table answers "which teacher takes Ada?". You need both — and that is what a join does.
SELECT Student.Name, Class.Teacher
FROM Student
INNER JOIN Class ON Student.ClassID = Class.ClassID;
Read it in four pieces:
SELECT Student.Name, Class.Teacher — the fields you want, each written as
Table.Field because two tables are in play.FROM Student — the first table.INNER JOIN Class — bring in the second table.ON Student.ClassID = Class.ClassID — the matching rule: pair a student row with the
class row that has the same ClassID.ON is almost always "the child's foreign key equals the parent's primary key". Get that pair
wrong and you get nonsense rows rather than an error, which is worse.
INNER JOIN keeps only pairs that match in both tables. A student whose ClassID has no
matching class does not appear at all. Neither does a class with no students.
When two tables both have a field called ClassID, ClassID on its own is ambiguous and
the DBMS rejects it. Write Student.ClassID or Class.ClassID to say which you mean.
Long table names make queries noisy, so SQL lets you nickname them:
SELECT S.Name, C.Teacher
FROM Student AS S
INNER JOIN Class AS C ON S.ClassID = C.ClassID
WHERE C.Teacher = 'Mr Okafor';
AS S creates the alias for this query only; nothing about the table changes. Mark schemes
accept either the full names or short aliases, as long as they are used consistently.
Worked example: Worked example: which teacher takes which student?
Class holds ClassID and Teacher. Student holds StudentID, Name and ClassID.
Write a query that lists each student's name and their teacher's name, sorted by teacher and then by student.
| ClassID | Teacher |
|---|---|
| 1 | Mr Okafor |
| 2 | Ms Lindqvist |
| 3 | Mr Adeyemi |
| StudentID | Name | ClassID |
|---|---|---|
| 1 | Ada | 1 |
| 2 | Ben | 2 |
| 3 | Cleo | 1 |
| 4 | Dev | 2 |
| 5 | Eve | 1 |
| 6 | Finn | 4 |
| 7 | Gus | 1 |
A join can feed straight into the aggregates from the previous lesson. To count students per teacher:
SELECT Class.Teacher, COUNT(*) AS Students
FROM Student
INNER JOIN Class ON Student.ClassID = Class.ClassID
GROUP BY Class.Teacher;
The order of the clauses never changes: FROM and JOIN first, then WHERE, then GROUP BY,
then ORDER BY. The join builds one combined set of rows; everything after it works on that
set.
Watch the row counts. With INNER JOIN, Mr Adeyemi's class 3 has no students, so there is no
row to count and he does not appear at all — not even with a zero. If a question asks for
"every teacher, including those with no students", INNER JOIN is the wrong tool.