How to Join 3 Tables (or More) in SQL

Using JOIN in SQL doesn’t mean you can only join two tables. You can join 3, 4, or even more! The possibilities are limitless.

The best way to practice SQL JOINs is LearnSQL.com's interactive SQL JOINs course. It contains over 90 hands-on exercises that let you refresh your SQL JOINs knowledge. It covers a wide range of topics from simple 2-table JOINs, through joining multiple tables and using OUTER JOINs, to joining a table with itself.

If you’ve just learnt JOINs in SQL, you might think that it’s limited to two tables. That’s not surprising – this concept can be hard to understand, and the idea that JOINs can get even more complicated may be really scary at first. The truth is that you can easily extend this idea to three tables or even more. Look at the query below:

SELECT
  student.first_name,
  student.last_name,
  course.name
FROM student
JOIN student_course
  ON student.id = student_course.student_id
JOIN course
  ON course.id = student_course.course_id;

We’ve simply repeated the JOIN clause and joined three tables. We’ll get deeper into the query and tables in the next section.

Getting to Know the Data

First, let’s introduce a few tables. Here’s the schema:

In the above entity-relationship diagram (ERD), you can see tables, their columns, the columns’ data types, and the references between tables. For example, there’s a reference between the student and student_course tables – each student can be linked to multiple rows in the student_course table. For further information about reading a schema, see the article Crow’s Foot Notation on the Vertabelo blog.

Our example data is stored in four tables.Right now, we’ll focus on the first three tables:

• student – Contains information about students:
  • id – The ID of the student.
  • first_name – The first name of the student.
  • last_name – The last name of the student.
• student_course – Contains information about which students go to which courses:
  • student_id – The ID of the student.
  • course_id – The ID of the course.
• course – Contains information about the courses:
• id – The ID of the course.
• name – The name of the course.
• teacher_id – The ID of the teacher for that course.

We’ll use the teacher table later in the course in more advanced queries. I’ll explain it then. Meanwhile, take a look at the example data from these three tables:

student

student_course

course

Junction Tables

It’s important to notice that the student_course table is a junction table. The sole purpose of this table is to connect the student and course tables together.

For example, “Shreya Bain” (the student with id = 1) is connected to “English literature” (the course with id = 2) and “Python programming” (the course with id = 3). How do we know this? Take a look at the first two rows in the student_course table. The first row says that the student with the ID equal to 1 (student_id column) is connected to a course with the ID equal to 2 (course_id column). From the second row, we see that the student with the ID equal to 1 is connected to the course with the ID equal to 3. Then, looking at the student table, we can read that Shreya Bain has an ID equal to 1. Finally, looking at the course table, we can read that English literature and Python programming have IDs equal to 2 and 3, respectively.

The relationship between the student and course tables is called a many-to-many relationship. One student can be attending many courses (i.e. many rows with the same student_id can be in the table student_course) and one course can be attended by many students (i.e. many rows in the table student_course can have the same course_id).

We’ve already seen the usage of the junction table. Take a look at the code again:

SELECT
  student.first_name,
  student.last_name,
  course.name
FROM student
JOIN student_course
  ON student.id = student_course.student_id
JOIN course
  ON course.id = student_course.course_id;

As you can see, we’re using the student table in the FROM clause. Then, we’re joining it with the student_course table and, finally, with the course table. This way, we can show the first and last name of each student together with the courses they’re attending. Is the code still confusing? Fear not – we’ll explain it thoroughly in the next section.

The result of this query will look like this:

If you want to write your own queries that join multiple tables, you need to fully understand what’s happening in this query. Let’s break our query into steps.

Joining 3 Tables Using a Junction Table

Step 1

The first step is to look at the schema and select the columns we want to show. Since we want to show students together with their courses, we’ll need three columns: student.first_name, student.last_name, and course.name.

It’s important to use table names when listing your columns. That way, you won’t get lost in different column names and you’ll immediately know which column belongs to which table.

At this point, our query should look like this:

SELECT
  student.first_name,
  student.last_name,
  course.name

Step 2

The next step is to determine which tables will be necessary for the query. There are two obvious ones: student and course. However, we’ll have to find a way to join these tables. Looking at the database schema, we see that student_course is a junction table between these two. So, we’ll need that table too.

Step 3

In the final part, we’ll have to join all the tables together. The first task is to choose the table which will go in the FROM clause. In theory, it can be any of the tables we’re using. Personally, I like starting with a table that isn’t a junction table. In this case, let’s go with the student table.

SELECT
  student.first_name,
  student.last_name,
  course.name
FROM student

Now, we can’t join the course table just yet. There isn’t a direct connection between these two tables. Because of it, we’ll have to go with the student_course table. We simply have to connect these two tables together using the JOIN … ON … statement. Our code takes shape:

SELECT
  student.first_name,
  student.last_name,
  course.name
FROM student
JOIN student_course
  ON student.id = student_course.student_id

Before we move on to adding the last table, we should think about what we’ve already achieved. Note that while writing a JOIN clause, we aren’t limited to the columns in the SELECT clause – we have access to all the columns! So, then, our query looks like this:

SELECT
  student.first_name,
  student.last_name,
  student.id,
  student_course.student_id,
  student_course.course_id
FROM student
JOIN student_course
  ON student.id = student_course.student_id;

This query shows almost all the columns we can use while writing the next JOIN statement. (I removed the student_course.id column, as we won’t need it.) Take a look at the data we’re working with:

This is how our data looks midstep. It’s often good to think about the data at this point. You sometimes may want to consider writing such a query from time to time just to analyze the rows and columns.

The above result should clearly show what to do next. We have students connected with the IDs of the courses they’re taking. The only thing we need is to add is course information. We know that the course_id column is in the student_course table. We have to join it with the id column from the course table. The resulting query looks like this:

SELECT
  student.first_name,
  student.last_name,
  course.name
FROM student
JOIN student_course
  ON student.id = student_course.student_id
JOIN course
  ON course.id = student_course.course_id;

And we’ve done it! That’s the query we wanted to write. Just don’t forget about the semicolon at the end of your code.

In this example, we’ve analyzed how to write a query with equi JOINs – we’re using equality in our joining conditions. This is the most common type of JOIN. However, you can also use non-equi JOINs. If you don’t know this term, I recommend checking out An Illustrated Guide to the SQL Non-Equi Join on the LearnSQL.com blog.

Joining SQL Tables Without a Junction Table

When you’re joining more than two tables, you won’t always have a junction table. But before we analyze an example query for this technique, let’s check the last table in our schema.

• teacher – Contains information on teachers:
  • id – The ID of the teacher.
  • first_name – The first name of the teacher.
  • last_name – The last name of the teacher.

And here’s what the teacher table looks like:

Now, given the data, we’d like to show each teacher with their students. Each teacher-student pair should be shown only once (e.g. if a teacher has more than one course with a student, the teacher should be shown only once with the student in the result).

This query is pretty similar to the previous one. Therefore, we’ll follow the same steps as before.

Step 1

First, we select the columns: teacher.first_name, teacher.last_name, student.first_name, and student.last_name. Then, we choose the necessary tables. This time, it’ll be all the tables from our schema: student, student_course, course, and teacher.

Step 2

Now, we have to join all the tables. As I said before, we can start with any table, but I prefer starting from one of the sides. Last time we put the student table in the FROM clause. This time, we’ll use the teacher table. Before writing any JOINs, our query will look like the one below. (Note the DISTINCT keyword; since we want to show distinct pairs of teacher-student, the keyword is extremely important.)

SELECT DISTINCT
  teacher.first_name,
  teacher.last_name.
  student.first_name,
  student.last_name
FROM teacher

Step 3

Now, joining the tables isn’t much different from the previous example. We just have to use the JOIN clause one more time. However, before we do so, let’s take a look at the data after joining the teacher and course tables:

SELECT
  teacher.first_name,
  teacher.last_name,
  teacher.id,
  course.teacher_id,
  course.name,
  course.id
FROM teacher
JOIN course
  ON teacher.id = course.teacher_id;

You can think of it as one table. In fact, it’s a little bit of an extended version of the course table.

Joining two additional tables is nearly the same as the process we used earlier. You simply have to add the same two JOINs as before. You just have to keep in mind that the JOINs should be written in the correct order. While joining, you can’t use columns from not-yet-introduced tables.

SELECT DISTINCT
  teacher.first_name,
  teacher.last_name.
  student.first_name,
  student.last_name
FROM teacher
JOIN course
  ON teacher.id = course.teacher_id
JOIN student_course
  ON student.id = student_course.student_id
JOIN student
  ON student_course.course_id = course.id;
JOIN student_course
  ON course.id = student_course.student_id
JOIN student
  ON student_course.course_id = student.id;

In the crossed-out part, I’ve copied some code from the first query where we joined three tables. In this case, the code was wrong; even though the conditions were correct, we were using not-yet-introduced tables. For example, while joining student_course table, we used the student table, which was introduced later.

Below the crossed out code, you can see the correct JOIN order. We join the student_course and course tables first. Then, using the student_course table, we can join the student table. This way, we introduce each table before using it in a JOIN … ON condition. Always remember this important rule!

The result of the above query will look like this:

In this case, we’ve used an INNER JOIN. This means that if the teacher doesn’t have any students, they won’t appear in the results. Of course, you can replace the INNER JOIN with any other JOIN type, e.g., LEFT OUTER JOIN. If you’d like to read more about LEFT JOINs, check out How to LEFT JOIN Multiple Tables in SQL on LearnSQL.com.

Basics Are Key to 3-Way JOINs

As you can see, joining three tables in SQL isn’t as hard as it sounds. In fact, you can join as many tables as you like – the idea behind it is the same as joining only two tables.

It’s very helpful to take a look at the data midstep and imagine that the tables you’ve already joined are one table.

To succeed at complex JOINs, it’s important to fully understand basic JOINs. Knowing them well will allow you to write extremely complex JOIN statements. And remember – practice makes perfect. If you need more explanation or exercises on JOINs in SQL, take a look at the SQL JOINs course at LearnSQL.com.