Velamuvi AIAcademy

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
In this lesson you will learn
  • 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.

Try it yourselfSQL
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.

  1. Start with enrollments: 18 rows of numbers.
  2. Join students: each row gains the student's details. Still 18 rows.
  3. 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.

Try it yourselfSQL
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.

Try it yourselfSQL
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.

Try it yourselfSQL
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?

Try it yourselfSQL
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?

Try it yourselfSQL
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.

Try it yourselfSQL
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.

Try it yourselfSQL
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

Practice exercise

Join all three tables. Show the student's name and the course title for every enrollment with progress below 100.

Your solutionSQL
-- Show the student name and course title for every enrollment
-- that is NOT finished yet (progress below 100).
-- Columns: name, title
Knowledge check

You join enrollments (18 rows) to students using the correct keys. How many rows does the result have?

Summary

  • Add another JOIN ... ON for 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 BY answer 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.