Skip to content
Related Articles

Related Articles

How to Left Join Multiple Tables in SQL
  • Difficulty Level : Medium
  • Last Updated : 06 Apr, 2021

Left Join is one of the Keywords used while writing queries in SQL. In SQL we normally use Join for the purpose of forming a new table by taking out common data like rows or records or tuples from both the tables which are having matching records in general. 

Here when it comes to Left Join in SQL it only returns all the records or tuples or rows from left table and only those records matching from the right table.

Syntax For Left Join:

SELECT column names
FROM table1
LEFT JOIN table2
ON table1.matching_column = table2.matching_column;

 Note: For example, if you have a left table with 10 rows, you are guaranteed to have at least 10 rows after applying join operation on two tables.

Consider two tables: 



1. Employee (Left Table) :

Emp_IdFirst_NameLast_NameGenderAgeDate_of_join
1PranayThanneruM452000-10-09
2SanthoshPrabhuM481999-07-10
3MaryclaraF342008-08-26
4JaneVatsalF302006-02-31
5HardikprabhuM222012-07-23

 2. Projects (Right Table) :

iddateProject_NoEmp_idNo_of_hours_worked
12005-03-151473162
22005-03-162322192
32005-03-172761198

To Join these two tables and to obtain common information we need to use the following query

SELECT E.Emp_id, E.First_Name, E.Last_Name, E.Gender, E.age, E.Date_of_join,    
       P.date AS Project_Assigned_date, P.No_of_hours_worked AS hours_worked
FROM Employee E
LEFT JOIN Projects P
ON E.Emp_id = P.Emp_id
GROUP BY E.Emp_id;

After execution of query the obtained table will be like:

Emp_IdFirst_NameLast_NameGenderAgeDate_of_joinProject_Assigned_datehours_worked
1PranayThanneruM452000-10-092005-03-17198
2SanthoshPrabhuM481999-07-102005-03-16192
3MaryclaraF342008-08-262005-03-15162
4JaneVatsalF302006-02-31[NULL][NULL]
5HardikprabhuM222012-07-23[NULL][NULL]
  • Once after obtaining the table as you can see that the Emp_id who is not assigned for a project who’s  Project_Assigned_date has became NULL and No_of_hours_worked also became NULL cause the Employee has not assigned anything to do.
  • Here Left Join mean in the sense based on above tables it took data from both the table rows which are matching and it also returned the values for the rows who’s data is not present in Table 2 as NULL cause we need to consider all the data of Left table.

Multiple LEFT JOIN’s in One Query:
Sometimes you need to LEFT JOIN more than two tables to get the data required for specific analyses. Fortunately, the LEFT JOIN keyword can be used with MULTIPLE TABLES in SQL.

Consider a table called Salary:

id Emp_idSalary_IncDate
15500002015-01-01
21650002015-01-01
32550002015-01-01

Here we combine the data from these tables Employee, Projects and Salary.

To do this the query need to be written in the below format:



SELECT E.Emp_id, E.First_Name, E.Last_Name, E.Gender, E.age, E.Date_of_join,  
   P.No_of_hours_worked AS hours_worked, S.Salary_inc AS Salary_Increment
FROM Employee E
LEFT JOIN Projects P
ON E.Emp_id = P.Emp_id
LEFT JOIN Salary S
ON E.Emp_id = S.Emp_id;

And the resulting table looks like after multiple Left Join:

Emp_idFirst_NameLast_NameGenderageDate_of_joinhours_workedSalary_Increment
1PranayThanneruM452000-10-0919865000
2SanthoshPrabhuM481999-07-1019255000
3MaryclaraF342008-08-26162[NULL]
4JaneVatsalF302006-02-31[NULL][NULL]
5HardikPrabhuM222012-07-23[NULL]50000

Hence you can see that we have combined the data from three tables into one single table using Left Join multiple times.

Consider one more Table called Experience:

idEmp_nameExperience
1Pranay5
2Santhosh4
3Mary3

Here we combine the data from these tables Employee, Projects and Experience.

To do this the query need to be written in the below format:

SELECT E.Emp_id, E.First_Name, E.Last_Name, P.date AS Project_Assigned_date, 
E1.Experience AS EXP
FROM Employee E
LEFT JOIN Projects
 P
ON E.Emp_id = P.Emp_id
LEFT JOIN Experience E1
ON E.Emp_id = E1.id;

And the resulting table looks like after multiple Left Join:

Emp_idFirst_NameLast_NameProject_Assigned_dateEXP
1PranayThanneru2005-03-175
2SanthoshPrabhu2005-03-164
3Maryclara2005-03-153
4JaneVatsal[NULL][NULL]
5HardikPrabhu[NULL][NULL]

As you can see, the LEFT JOIN in SQL can be used with multiple tables. 

My Personal Notes arrow_drop_up
Recommended Articles
Page :