How to Join 3 Tables in SQL?
FREE
In SQL, we can join n number of tables to produce and fetch the desired result from the SQL queries. You can use JOIN in SQL to join two tables. Also, using JOIN in SQL doesn't mean you can join only two tables. It's not as difficult as it sounds to join more than two tables in SQL. You can join 3 tables in SQL using two JOIN keywords and this is explained with the examples below.
In this article, we'll examine various techniques for merging three tables together while keeping the value of n equal to three. You can join 3 tables in SQL using the JOIN clause in SQL. This method can also be used if you want to join more than 3 tables.
Joining 3 Tables Using a Junction Table
When using three tables, one of them is the Junction table, whose primary purpose is to join the other two tables together. In this article, we'll understand with an example how you can use the junction table to merge the three given tables. You can have a look at the junction table below and observe that atleast one of the columns from the junction table is also present in the other two tables so that two tables can JOIN with the help of the JUNCTION table.
[IMAGE 1 FINISH SAMPLE]
To extract the necessary rows, many queries can be developed, but if the dataset contains a large number of rows, they are not efficient and take a lot of time. Therefore, it is best to only write one line of the query.
SQL has 4 types of JOINS i.e., INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN. These different types of JOINS can be leveraged accordingly to join 3 tables. In the below examples, we'll use the LEFT JOIN in SQL.
Some points to consider here are:
- All the 3 tables must have some relationship with each other through a foreign key.
- Each table must have a common column of data.
- The name and datatype of the common column can be same the or different.
- The common column of the first table needs to be Primary Key and that of the second table needs to be the Foreign Key of that table.
Now, let's take an example to understand how to join 3 tables in SQL using a junction table. For example, let's consider the below-given tables that are available in our schema and all the examples make use of the below tables only.
Example:
Employees:
| emp_id | emp_name | emp_age | manager_id |
|---|---|---|---|
| 1 | Roan Kent | 29 | 1010 |
| 2 | Beau Merrill | 31 | 1015 |
| 3 | Reiss Mullen | 29 | 1010 |
| 4 | Neve Michael | 28 | 1095 |
| 5 | Fateh Mcgrath | 30 | 1020 |
| 6 | Saima Joyner | 31 | 1050 |
| 7 | Nastya Drova | 32 | 1015 |
| 8 | Mark Dwell | 30 | 1095 |
| 9 | Lisa Maywell | 31 | 1030 |
Department:
| dept_id | dept_name | dept_location | manager_id |
|---|---|---|---|
| 201 | Finance | Russia | 1050 |
| 401 | Admin | India | 1010 |
| 501 | Finance | Netherlands | 1020 |
| 601 | IT | Ireland | 1095 |
| 801 | Sales | UAE | 1050 |
| 901 | IT | USA | 1020 |
Salary:
| dept_id | dept_location | salary_grade | min_salary | max_salary |
|---|---|---|---|---|
| 401 | UAE | II | 30000 | 60000 |
| 901 | Russia | III | 40000 | 50000 |
| 801 | Netherlands | IV | 30000 | 60000 |
| 501 | USA | III | 30000 | 50000 |
| 201 | Ireland | I | 60000 | 80000 |
| 601 | India | II | 40000 | 70000 |
Information:
| id | emp_country | manager_profile | |
|---|---|---|---|
| 1 | USA | Laissez-faire | |
| 5 | Netherlands | Democratic | |
| 8 | UAE | Servant | Leadership |
Now, in this example we want to JOIN the Employees and Salary table but as there aren't any common columns between the two tables so we have to use a junction table i.e. Department table. The department table helps to merge the Employees and Salary table as the department table has such columns which are present in both the Employees and the Department table.
Let's take a look at the SQL query below,
SQL
Here is the result of the above query,
Output:
Joining SQL Tables Without a Junction Table
Now, we'll join the Employees and Information table. As you can observe that there are no columns present which is common so we'll use all the tables in the schema to join the tables.
Let's take an example in which the junction table is not present but still you can join the tables via another table.
Example:
The result of the above query is given below:
Output:
Learn More
Conclusion
- JOIN statements with ON conditions are one of the essential techniques for joining three tables.
- As you learned from the article that joining three tables in SQL isn't hard as it sounds. Although you can join as many tables as you want and the idea behind it is the same as joining two tables.
- If there is a junction table then joining two or more tables could be an easy task.
- If there are not any junction tables present in the schema then also you can join the tables after finding similar columns in the table that are present in the schema.