Back to list Standard SQL How to Join Multiple (3+) Tables in One Statement Database: SQL MySQL MS SQL Server PostgreSQL Oracle SQLite 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: SQL Basics SQL Basics in SQL Server SQL Practice Set Recommended articles: SQL JOINs for Beginners An Illustrated Guide to Multiple Join Learning JOINs With Real World SQL Examples See also: How to Join on Multiple Columns How to Multiply Two Columns in SQL Tags: SQL MySQL MS SQL Server PostgreSQL Oracle SQLite Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.