Join Multiple Tables in SQL

Learn how to join multiple tables in SQL.

Overview

To combine data from more than 2 tables, we use multiple table joins. The join operator is used multiple times to join multiple tables in SQL as for each new table one join is added. In SQL, joining multiple tables means you can join n number of tables but, to join n number of tables, the joins required are n−1, that is for 33 tables, 32 joins are required.

Introduction

Often, we need to get data from three or more tables to get some meaningful information. This can be accomplished using a multiple-table join.

Data can be retrieved using different types of joins like INNER JOIN, LEFT JOIN, or both based on different requirements. Let’s find out how to use them. Let’s say there are three tables in our database Student, Branch and Address.

Student Table
The Student table contains Stud_id as primary key, Name, Br_id as foreign key referring Branch table, Email and City_id as foreign key referring Address table.

Query:

Branch Table

The Branch table contains Br_id as primary key, Br_name, HOD and Contact.

Note: You can use the below query to create the above table.

Address Table The Address table contains city_id as the primary key, City and Pincode.

Note can use the below query to create the above table.

Join multiple tables using INNER JOIN

In SQL, an inner join obtains common rows from all of the tables mentioned in a query. In our case, if we see Student table and Branch tables has Br_id common and Address table and Student table has city_id common. So, to retrieve data, first, we need to join two tables and then the third table.

Let’s take an example to make it more clear.

Example: Obtain Names of students along with their branch name, their HOD, city, and Pincode.

Query:

Output:

Explanation: So to retrieve data from more than two tables we need to use the JOIN condition multiple times. The first join creates the virtual table which has data from the first two tables and then the second JOIN condition is applied to that virtual table. Here, we first INNER JOIN two tables Branch and Student using Br_id then another INNER JOIN added to the output of the first two tables using city_id.

Note: A virtual table is a table that is created in memory, when we join 3 tables, the result from joining the first two tables is stored in a virtual table and when the second join is applied, it is applied on the virtual table.

Join Multiple Tables Using LEFT JOIN

When we use left join on the multiple tables, it will include all the rows from the left table that is the table mentioned on the left side of the join and the next left joins will be applied to the result of the first left join.

Let’s take an example to understand this.

Example: Obtain all the names of the students even if they are not present in any City and Branch.

Query:

Output:

Explanation: In this query, the first left join gets all the rows with the names of students from the Student table along with all the Br_name. If the Name is not present in any branch the Br_name field we get for that Name is NULL. On this table, another left join is applied on the Address table which keeps all the Name and Br_name as it is and returns all rows from the city column. If the Name and Br_name are not present in any City the City field we get for those Name and Br_name is Null.

Join multiple tables using both — INNER JOIN & LEFT JOIN

The combination of inner join and left join can be used in SQL query where the order of join decides the result of the query.

Let’s take an example to understand this.

Example: Obtain all the Student Names even if they are not present in any Branch whereas excluding the Name which is not present in any city.

Query:

Output:

Explanation:
In this query, first all the names from the Student table are returned and the Br_name is returned even if the name does not have any Br_name by using left join. On this result, an inner join is applied with the Address table which eliminates the name with no city.

Using Parent-Child Relationship

The parent-child states the Primary key Foreign key relationship between two tables. The table has a primary key is stated as parent and table with foreign key is stated as a child. Using this method you can simply join multiple tables in SQL.

Let’s take the same example we took for inner join and solve it by the parent-child relationship method.

Example: Obtain Names of students along with their branch name, their HOD, city, and Pincode.

Query:

Output:

Explanation: As you can see, the output from the inner join query and this query are the same. In this query, Br_id is the primary key of the Branch table and the foreign key of the Student table. Similarly, City_id is the primary key to the Address table and the foreign key to the Student table. This is an alternative approach to an inner join.

Conclusion

• The multiple joins in SQL can be performed by using LEFT JOIN, INNER JOIN, and a combination of both INNER & LEFT JOIN.
• We can also join multiple tables in SQL using the parent-child relationship which is equivalent to an inner join.