Articles Cookbook
Back to list
Standard SQL

How to Join Multiple (3+) Tables in One Statement

Database:

Operators:

JOIN, INNER JOIN, ON

Problem:

You’d like to combine data from more than two tables using only one SELECT statement.

Example:

There are four tables in our database: student, teacher, subject, and learning.

The student table contains data in the following columns: id, first_name, and last_name.

idfirst_namelast_name
1TomMiller
2JohnSpring
3LisaWilliams
4EllieBarker
5JamesMoore

The teacher table contains data in the following columns: id, first_name, last_name, and subject.

idfirst_namelast_name
1MilanSmith
2CharlesDavis
3MarkMoore

The subject table contains data in the following columns: id and name.

idname
1English
2Art
3Music

Finally, the learning table contains data in the following columns: id, mark, subject_id, student_id, and teacher_id.

idmarksubject_idstudent_idteacher_id
14121
25232
34313
43212
52353
63342

We want to know which students are studying English, music, and art, as well as which teachers are instructing these classes. Select the course subject, the last name of the student taking that course, and the last name of the teacher delivering that course.

Solution:

Use multiple JOINs in your query:

SELECT l.name AS subject_name,   
  t.last_name AS student_last_name, 
  st.last_name AS teacher_last_name 
FROM learning AS l  
JOIN subject s ON l.subject_id=s.id
JOIN student st ON l.student_id=st.id
JOIN teacher t ON l.teacher_id=t.id; 

This query returns records with the name of the course subject and the last names of the students and teachers:

subject_names_last_namet_last_name
MusicMooreMiller
ArtDavisMiller
EnglishSmithSpring
ArtDavisWilliams
MusicDavisBarker
MusicMooreMoore

This data comes from three tables, so we have to join all those tables to get the information we seek.

Discussion:

If you’d like to combine data stored in multiple (more than two) tables, you should use the JOIN operator multiple times. First, you join two tables as you normally would (using JOIN, LEFT JOIN, RIGHT JOIN, or FULL JOIN, as appropriate). The JOIN operation creates a “virtual table” that stores combined data from the two tables. In our example, the result table is a combination of the learning and subject tables.

The next step is to join this result table to the third table (in our example, student). This is like a regular JOIN: you join the “virtual table” and the third table with an appropriate condition. This condition should generally include one or more columns from the additional table (student) and one or more columns from the “virtual table”. In our example, we reference the student table in the second JOIN condition.

At this point, we have a new virtual table with data from three tables. The last step is to add data from the fourth table (in our example, teacher). and join using the key from these tables (in our example, id from the teacher table and teacher_id from the learning table).

If you have to join another table, you can use another JOIN operator with an appropriate condition in the ON clause. In theory, you can join as many tables as you want.

Recommended courses:

Recommended articles:

See also:

go to top