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.

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:

SELECT 
  player.name, 
  team.name, 
  coach.name
FROM player
JOIN team
ON player.team_id = team.id
JOIN coach
ON team.coach_id = 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 = team.id

Likewise, the join between the team and the coach table is ON team.coach_id = 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:

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:

SELECT player.name, team.name, coach.name
FROM player
JOIN team
ON player.team_id = team.id
JOIN coach
ON team.coach_id = coach.id;

We will get the following table as a result:

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:

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:

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:

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.

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

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:

SELECT 
  author.name, 
  book.name
FROM author
JOIN author_book
ON author.id = author_book.author_id
JOIN book
ON book.id = author_book.book_id
ORDER BY author.name;

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:

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?

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:

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:

SELECT 
  doctor.name, 
  appointment.date, 
  appointment.time, 
  patient.name
FROM doctor
JOIN appointment
ON doctor.id = appointment.doctor_id
JOIN patient
ON appointment.patient_id = 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:

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:

• Learn some tips on how to practice SQL JOINs.
• Discover examples of SQL JOINs with detailed explanations.
• Understand how inner SQL JOIN works.

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