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.
Contact Us