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

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.

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

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

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

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:

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.