A join links rows from two tables where the foreign key matches the primary key. An INNER JOIN keeps only the rows that have a match in both tables.
This lesson follows normalising a small data set. Next comes aggregation and filtering.
How is a join written?
SELECT Student.Name, House.HouseName
FROM Student
INNER JOIN House
ON Student.HouseID = House.HouseID;The ON line names the matching columns. Writing the table name before the column, such as Student.HouseID, shows which table each column comes from.
Worked example: students and houses
Original Student table:
| StudentID | Name | HouseID |
|---|---|---|
| 1 | Aina | 1 |
| 2 | Bala | 2 |
| 3 | Chen | 1 |
| 4 | Dina | 2 |
Original House table:
| HouseID | HouseName |
|---|---|
| 1 | Merah |
| 2 | Biru |
| 3 | Hijau |
Predict the result. Each student row matches the house with the same HouseID. Aina and Chen match Merah, and Bala and Dina match Biru.
| Name | HouseName |
|---|---|
| Aina | Merah |
| Bala | Biru |
| Chen | Merah |
| Dina | Biru |
There are four rows. Hijau does not appear, because no student has HouseID 3.
The mistake that costs marks
The common slip is to leave out the ON condition. The system then pairs every student with every house.
| Query | Rows returned |
|---|---|
| with ON Student.HouseID = House.HouseID | 4 |
| without a matching condition | 4 × 3 = 12 |
The twelve rows show Aina with Merah, Biru and Hijau, which is wrong. The fix is to count: a correct join on a one-to-many link returns at most as many rows as the child table has.
Check yourself
The Student table above gains a fifth student, Emir, with HouseID 3. How many rows does the same INNER JOIN return, and what is Emir’s house?
Answer
It returns five rows. Emir has HouseID 3, which matches Hijau, so his row is Emir and Hijau. Hijau now appears in the result because a student belongs to it.
What to study next
After joining, you usually count or total the results. Continue with using aggregation and filtering, then slow down with SQL reasoning in a fictional database.
If you want a teacher to go through joins with you, see online one-to-one Computer Science tuition.