Join Multiple Tables in SQL

Introduction

We often need to get data from three or more tables to get 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 us assume that there are three tables in our database Student, Branch, and Address.

Student Table

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

Query:

Branch Table

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

Address Table

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

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 the Student and Branch table have Br_id common and the Address 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 students' names along with their branch names, 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.

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.

Read More:

• Difference Between SQL and NoSQL.