Back to articles list April 21, 2020 - 10 minutes read How to Join 3 Tables (or More) in SQL Kamil Bladoszewski Kamil works as a Java developer and is a machine learning hobbyist. He loves clean code and turning interesting data into effective charts. His goal of giving people access to knowledge is fulfilled by being a member of the ML in PL Association, an organization that supports the machine learning community in Poland. In his free time, he likes playing computer games. Tags: sql learn sql joins 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. 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. But before we continue, I strongly recommend that you should fully understand SQL JOINs. The concept I’m about to explain relies strongly on basic knowledge. If you’re not at that level yet, check out the SQL JOINs course at LearnSQL.com. 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 idfirst_namelast_name 1ShreyaBain 2RiannaFoster 3YosefNaylor student_course student_idcourse_id 12 13 21 22 23 31 course idnameteacher_id 1Database design1 2English literature2 3Python programming1 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: first_namelast_namename ShreyaBainEnglish literature ShreyaBainPython programming RiannaFosterDatabase design RiannaFosterEnglish literature RiannaFosterPython programming YosefNaylorDatabase design 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: first_namelast_nameidstudent_idcourse_id ShreyaBain112 ShreyaBain113 RiannaFoster221 RiannaFoster222 RiannaFoster223 YosefNaylor331 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: idfirst_namelast_name 1TaylahBooker 2Sarah-LouiseBlake 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; first_namelast_nameidteacher_idnameid TaylahBooker11Database design1 TaylahBooker11Python programming3 Sarah-LouiseBlake22English literature2 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: first_namelast_namefirst_namelast_name TaylahBookerShreyaBain TaylahBookerRiannaFoster TaylahBookerYosefNaylor Sarah-LouiseBlakeShreyaBain Sarah-LouiseBlakeRiannaFoster 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. Tags: sql learn sql joins You may also like How to Learn SQL JOINs Learn how to use SQL JOINs to effectively combine data across multiple tables and analyze sophisticated data sets. Read more Subquery vs. JOIN What are the differences between a subquery and a JOIN in SQL? How do you choose one over the other? See examples of when to use which. Read more How to Join 3 Tables (or More) in SQL Have you ever wondered how to join three tables in SQL? It's easy when you know the basics. Joining three tables can be as easy as joining two tables. Read more How to Practice SQL JOINs Do you want to master SQL JOINs? The best way is through practice. Here’s how to practice SQL JOINs along with some examples. Read more How to LEFT JOIN Multiple Tables in SQL You will learn how to left join 3 tables in SQL while avoiding common mistakes in joining multiple tables. Examples included! Read more SQL INNER JOIN Explained in Simple Words Learn how to join tables in SQL using the most famous among SQL JOIN types–the INNER JOIN. Read more Converting Subqueries to Joins No more badly performed queries! Find out how you can convert SQL subqueries to joins and improve your query efficiency. Read more An Illustrated Guide to Multiple Join Did you wonder what is the most efficient way of making multiple joins in SQL? We did as well - come by and check out our illustrated guide! Read more How to LEFT JOIN Multiple Tables in SQL You will learn how to left join 3 tables in SQL while avoiding common mistakes in joining multiple tables. Examples included! Read more Learning JOINs With Real World SQL Examples JOIN statement lets you work with data stored in multiple tables. This article is a practical introduction to the SQL JOIN. Check out examples in real life. Read more An Illustrated Guide to the SQL Self Join What is a SQL self join and how does it work? When should you use a self join in SQL? In this article, you’ll find answers to these questions! Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.