Multi-table Joins
You can join three or more tables in a single SQL query by chaining multiple JOIN operations.
This is common in normalized databases where data is separated across related tables.
By default, when you just write JOIN without specifying the type, most SQL databases interpret it as an INNER JOIN.
Syntax
Here is the syntax for a multi-table join:
SELECT ...
FROM table1
JOIN table2 ON ...
JOIN table3 ON ...;
Each JOIN connects one additional table to the growing result set.
Make sure each join condition (ON ...) clearly defines how the tables relate.
Example: Students, Enrollments, and Classes
As an example, let's say we have the following tables:
students
| student_id | name |
|---|---|
| 1 | Alex |
| 2 | Sara |
| 3 | David |
| 4 | Nina |
| 5 | Michael |
enrollments
| student_id | class_id |
|---|---|
| 1 | A1 |
| 2 | A2 |
| 3 | A1 |
| 4 | A3 |
| 5 | A2 |
classes
| class_id | class_name |
|---|---|
| A1 | Math |
| A2 | History |
| A3 | Physics |
| A4 | Chemistry |
Below is an example of a multi-table join between the students, enrollments, and classes tables.
SELECT students.name, classes.class_name
FROM students
JOIN enrollments ON students.student_id = enrollments.student_id
JOIN classes ON enrollments.class_id = classes.class_id;
Result:
| name | class_name |
|---|---|
| Alex | Math |
| Sara | History |
| David | Math |
| Nina | Physics |
| Michael | History |
This query connects students to their enrollments, then maps those to class names.
When to Use Multi-table Joins
Use multi-table joins when:
- You need a complete view across multiple related tables
- You're working with a normalized schema
- You're building reports that require joined context
Tip: Use clear and descriptive aliases to keep your queries readable.
Want to learn more?
Join CodeFriends Plus membership or enroll in a course to start your journey.