How to Left Join Multiple Tables in SQL

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_Id	First_Name	Last_Name	Gender	Age	Date_of_join
1	Pranay	Thanneru	M	45	2000-10-09
2	Santhosh	Prabhu	M	48	1999-07-10
3	Mary	clara	F	34	2008-08-26
4	Jane	Vatsal	F	30	2006-02-31
5	Hardik	prabhu	M	22	2012-07-23

 2. Projects (Right Table) :

id	date	Project_No	Emp_id	No_of_hours_worked
1	2005-03-15	147	3	162
2	2005-03-16	232	2	192
3	2005-03-17	276	1	198

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_Id	First_Name	Last_Name	Gender	Age	Date_of_join	Project_Assigned_date	hours_worked
1	Pranay	Thanneru	M	45	2000-10-09	2005-03-17	198
2	Santhosh	Prabhu	M	48	1999-07-10	2005-03-16	192
3	Mary	clara	F	34	2008-08-26	2005-03-15	162
4	Jane	Vatsal	F	30	2006-02-31	[NULL]	[NULL]
5	Hardik	prabhu	M	22	2012-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_id	Salary_Inc	Date
1	5	50000	2015-01-01
2	1	65000	2015-01-01
3	2	55000	2015-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_id	First_Name	Last_Name	Gender	age	Date_of_join	hours_worked	Salary_Increment
1	Pranay	Thanneru	M	45	2000-10-09	198	65000
2	Santhosh	Prabhu	M	48	1999-07-10	192	55000
3	Mary	clara	F	34	2008-08-26	162	[NULL]
4	Jane	Vatsal	F	30	2006-02-31	[NULL]	[NULL]
5	Hardik	Prabhu	M	22	2012-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:

id	Emp_name	Experience
1	Pranay	5
2	Santhosh	4
3	Mary	3

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_id	First_Name	Last_Name	Project_Assigned_date	EXP
1	Pranay	Thanneru	2005-03-17	5
2	Santhosh	Prabhu	2005-03-16	4
3	Mary	clara	2005-03-15	3
4	Jane	Vatsal	[NULL]	[NULL]
5	Hardik	Prabhu	[NULL]	[NULL]

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