Multi-table Joins
You can join three or more tables in a single SQL query by chaining multiple JOIN operations together.
This is common in normalized databases where data is separated across related tables.
By default, if you 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 adds another table to the growing result set.
Ensure that each join condition (ON ...) clearly defines how the tables are related.
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 using 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 and then maps those enrollments to class names.
Why are multi-table joins important?
Multi-table joins are essential when:
- You need a complete view across several related tables
- You’re working with a normalized schema where data is stored across multiple tables
- You’re building reports that require context from multiple sources
Tip: Use clear and descriptive aliases to keep your queries easy to read.
Want to learn more?
Join CodeFriends Plus membership or enroll in a course to start your journey.