Lesson 13 of 17 · Combining Tables
Joining Many Tables
Join three or more tables in one SQL query. Build multi-table joins step by step, then summarise them with GROUP BY to answer real questions.
- 8 min read
- Beginner
- Reviewed
- How to chain joins across three tables
- A step-by-step method for writing long queries
- How to summarise joined data with GROUP BY
- How to check that a join has not multiplied your rows
One more join
The enrollments table links students to courses. So far you have joined it to one side at a time. To see who is taking what, you need both: three tables in one query.
There is nothing new to learn. You add a second JOIN.
SELECT s.name, c.title, e.score FROM enrollments AS e JOIN students AS s ON s.id = e.student_id JOIN courses AS c ON c.id = e.course_id ORDER BY s.name, c.title;
For the first time, the result contains no id numbers at all. Every row reads like a sentence: this student, this course, this score.
How to think about it
Joins are applied one after another.
- Start with
enrollments: 18 rows of numbers. - Join
students: each row gains the student's details. Still 18 rows. - Join
courses: each row gains the course's details. Still 18 rows.
Each join follows one foreign key to the table it points to. You can keep going for as many tables as the question needs.
Build long queries in steps
Nobody writes a five-clause, three-table query in one go. Build it up, running it after each step.
Step 1. Start with the table in the middle and look at it.
SELECT e.student_id, e.course_id, e.score FROM enrollments AS e LIMIT 5;
Step 2. Add one join and check the result still makes sense.
SELECT s.name, e.course_id, e.score FROM enrollments AS e JOIN students AS s ON s.id = e.student_id LIMIT 5;
Step 3. Add the next join, then the filter and the sorting.
SELECT s.name, c.title, e.score FROM enrollments AS e JOIN students AS s ON s.id = e.student_id JOIN courses AS c ON c.id = e.course_id WHERE e.score >= 90 ORDER BY e.score DESC;
If something goes wrong, you know it was the step you just added.
Summarising across tables
Joins and GROUP BY together answer the questions people really ask. What is the average score in each course, by name?
SELECT c.title,
COUNT(e.id) AS enrolled,
ROUND(AVG(e.score), 1) AS average_score
FROM courses AS c
LEFT JOIN enrollments AS e ON e.course_id = c.id
GROUP BY c.id, c.title
ORDER BY enrolled DESC, c.title;A left join is used so that the course with no students still appears, with a count of 0 and an average of NULL.
How many enrollments does each subject area have?
SELECT c.category, COUNT(*) AS enrollments FROM enrollments AS e JOIN courses AS c ON c.id = e.course_id GROUP BY c.category ORDER BY enrollments DESC, c.category;
A question that needs everything
Which countries have completed the most courses? The country is in students, completion is in enrollments, and we only want subjects in the Programming category, which is in courses.
SELECT s.country, COUNT(*) AS completed FROM enrollments AS e JOIN students AS s ON s.id = e.student_id JOIN courses AS c ON c.id = e.course_id WHERE e.progress = 100 AND c.category = 'Programming' GROUP BY s.country HAVING COUNT(*) >= 1 ORDER BY completed DESC, s.country;
This single query uses every clause you have learned.
Check your row counts
A wrong join condition can silently multiply rows. Every enrollment might be paired with several rows instead of one, and every total after that is inflated.
The defence is a quick count before and after.
SELECT COUNT(*) AS enrollments_alone FROM enrollments; SELECT COUNT(*) AS after_both_joins FROM enrollments AS e JOIN students AS s ON s.id = e.student_id JOIN courses AS c ON c.id = e.course_id;
Both say 18. Following a foreign key to its primary key never adds rows, because each key matches exactly one row. If the second number were larger, a join condition would be wrong.
Common mistakes
Join all three tables. Show the student's name and the course title for every enrollment with progress below 100.
-- Show the student name and course title for every enrollment -- that is NOT finished yet (progress below 100). -- Columns: name, title
You join enrollments (18 rows) to students using the correct keys. How many rows does the result have?
Summary
- Add another
JOIN ... ONfor each extra table. - Each join follows one foreign key to the table it points to.
- Build long queries step by step, running them as you go.
- Joins combined with
GROUP BYanswer most real questions. - Count rows before and after a join to make sure nothing was multiplied.
You have finished the module on combining tables. Next, you will learn to change the data itself.
Finished reading? Tick it off to track your progress.