Back to articles list Articles Cookbook
8 minutes read

How to Write Multiple Joins in One SQL Query

Have you ever wondered how multiple joins work? Want to know how to join multiple tables in one query? Read this article to find out!

If you are just beginning your SQL journey, you may have found it difficult to understand the concept of SQL JOINs, especially if you have to join more than two tables in one query. Worry not! In this article, we will explore how JOIN works and solve all its mysteries!

The best way to learn SQL is through practice. If you want to master joining tables in SQL, check out our interactive SQL JOINs course. It offers over 90 hands-on exercises that will get you writing many different kinds of JOINs.

Let's Begin with an Example

Imagine that you have the following three tables: player, team, and coach.

How to Write Multiple Joins in One SQL Query

Let's build a query that will list the names of all players, the name of their team, and the name of their coach. You can get all of this information using the following query:

FROM player
JOIN team
ON player.team_id =
JOIN coach
ON team.coach_id =;

We have two different JOIN operations here. The tables player and team are joined first, then the coach table is joined as well.

To create a join condition, we need both joined tables to have a column that contains the same information. The team_id column in the player table contains the team_id; this is the same value as the id column in the team table. Hence, the first join condition is ON player.team_id =

Likewise, the join between the team and the coach table is ON team.coach_id =

If you have trouble remembering the exact syntax for SQL JOINs, make sure to bookmark our amazing SQL JOIN Cheat Sheet!

How Joining Multiple Tables Works

Let's say that we have the following data in our tables:

1Mark Swanson
2Alice Wright
1Ambitious Raptors2
2Fire Pandas1
3Steel Cats2
1John Rodger1
2Paul Smith1
3David McDonald2
4Sarah Grey3
5Sophie Brown3

Now, let's run the query that will list the names of all the players, the name of their team, and the name of their coach:

FROM player
JOIN team
ON player.team_id =
JOIN coach
ON team.coach_id =;

We will get the following table as a result:
John RodgerAmbitious RaptorsAlice Wright
Paul SmithAmbitious RaptorsAlice Wright
David McDonaldFire PandasMark Swanson
Sarah GreySteel CatsAlice Wright
Sophie BrownSteel CatsAlice Wright

Okay, but what really happens “under the hood” when two tables are joined? Let's take a closer look at our example.

First, the JOIN of the player and team tables forms a “virtual table”. It would look something like this:

1John Rodger11Ambitious Raptors2
2Paul Smith11Ambitious Raptors2
3David McDonald22Fire Pandas1
4Sarah Grey33Steel Cats2
5Sophie Brown33Steel Cats2

As you can see, this "virtual table" contains all of the columns from both the player and team tables.

Hey, we joined 3 tables in this example query! What happened to the coach table? That's easy! We already have a “virtual table” made of the player and team tables, so it looks like we are left with a join of two tables again – only this time we will join the coach table to the "virtual table". So, the final joined table will look like this:
1John Rodger11Ambitious Raptors22Alice Wright
2Paul Smith11Ambitious Raptors22Alice Wright
3David McDonald22Fire Pandas11Mark Swanson
4Sarah Grey33Steel Cats22Alice Wright
5Sophie Brown33Steel Cats22Alice Wright

The columns that belong to the "virtual table" (the join of the tables player and team) are shown in orange; the columns from the coach table are shown in blue.

Important: When we join another table, the join condition should include a column that matches a column from one of the previously joined tables. In our example, the coach table is joined to the virtual table using the coach's ID, which is present in the coach table and in the team table. Therefore, it was present in the "virtual table". You can imagine that the joined tables are “linked” to one another with joining conditions:

How to Write Multiple Joins in One SQL Query

Of course, we don't need to display all of the columns from the joined table. In JOIN queries, we usually specify which columns we would like to see. Note that we did that in the example query above: only the player's name, team's name, and coach's name were shown.

Junction Tables

A junction table is a table that links two or more tables. Many joins of multiple tables involve using a junction table. Sound vague? Let’s see it in action.

Imagine that you have the tables author and book. One author could have written many books and one book can have many authors. To model such a relationship (we call it a many-to-many relationship), we need a third table: author_book. The sole purpose of this table is to connect the author and book tables together. The author_book table is an example of a junction table.

How to Write Multiple Joins in One SQL Query

Let's say that we have the following data in our tables:

1Sarah Green
2Martin Davis
3Steve Johnson
1The Translucent Door
2Whisper of Dawn
3To Catch a Dream

If you want to join the author and book tables, you must also use the junction table author_book. For example, to list all authors and their books, you can write a query like this:

FROM author
JOIN author_book
ON = author_book.author_id
JOIN book
ON = author_book.book_id

First, the author_book table is joined with the author table to form a "virtual table". Then, the book table is joined to the "virtual table".

When joining tables linked by a junction table, make sure you join the non-junction tables to the junction table. In our example, author is joined with author_book. Then book is also joined. If you have a junction table in your database, the joins involving these tables will usually involve three or more tables.

The result of the query will look like this. We do not see any of the junction table’s columns in the result, yet joining it is essential to make the query work correctly:
Sarah GreenThe Translucent Door
Martin DavisThe Translucent Door
Sarah GreenWhisper of Dawn
Steve JohnsonWhisper of Dawn
Martin DavisTo Catch a Dream

A Junction Table with Additional Columns

In the example above, the only columns present in the junction table were the columns referencing other tables. However, this does not need to be the case! The junction table can contain other fields as well. Those fields store additional information about the relationship.

Let's take a look at an example. This time, we have the following 3 tables: doctor, patient and appointment. Can you guess which table is the junction table here?

How to Write Multiple Joins in One SQL Query

You're right! The junction table is the appointment table. It links doctors with their patients to create appointments. However, the appointment table also needs additional columns, such as appointment date and time.

Let's say that we have the following data in our tables:

1Ann Johnson
2Marlene Smith
3John West
1Mary Brown
2Sally Rodgers
3Mark Jackson

Now, let's build a query that will help the doctors manage their schedule: for each doctor, we would like to see all of their appointments' dates and times, plus the name of the relevant patient. You can get all of this information using the following query:

FROM doctor
JOIN appointment
ON = appointment.doctor_id
JOIN patient
ON appointment.patient_id =;

First, the appointment table is joined with the doctor table to form a "virtual table". Then, the patient table is joined to the "virtual table" by referencing the junction table appointment.

The result of the query would look like this:
Ann Johnson16/04/20238:00Mary Brown
Ann Johnson16/04/20239:00Mark Jackson
Marlene Smith17/04/20238:00Sally Rodgers
Marlene Smith17/04/20239:00Mary Brown
John West17/04/202316:00Mark Jackson

A 3-Way Junction Table

Junction tables can also link three or more tables. For example, imagine that a doctor works at various clinic locations. When you schedule an appointment, you are assigned a doctor, but you also need to choose the clinic where the appointment will take place. So, our appointment table would now link three tables: doctor, patient, and clinic.

In this scenario, many queries will require joining all four tables. In some situations, junction tables can link even more tables.

Let's join some tables!

Good job! You just learned how to use multiple JOINs in one SQL query.

Let's summarize what we've just learned:

  • A three-table JOIN is a JOIN of two tables with another join. The JOIN of two tables forms a “virtual table” to which the other table is joined.
  • The join condition is usually an equality between some columns in one table that share values with columns in the other table.
  • When you join more than two tables, in most cases you’ll need to make sure that the join condition “links” to the tables which have already been joined.
  • A junction table is a table that links two or more other tables by referencing the IDs of the relevant tables. When a junction table is involved, you’ll likely need to use a multi-table join.

If you're curious to know more about SQL JOINs, take a look at these articles on our blog:

And for some more practice, remember to check out our SQL JOINs interactive course!