Next Chapter »

SQL Tutorial - Left Join


'Left Join' is a type of join that displays all the rows of the left table along with the matching data on the right table.

The Left Join


The 'Left Join 'is a type of outer join. It is also known as LEFT OUTER JOIN. This join retrieves all the records from the left table and all the matching rows from the right table. It retrieves all the records from the left table even when there are no matches in the right table. When a row in the left table has no matching rows in the right table, the associated result set record will contains null values for all corresponding columns coming from the right table.

SQL 'Left Join' Syntax


Select tableA.column1,tableA.column-2,tableA.column-3,...tableA.column-n,tableB.column1,
tableB.column2,.....tableB.columnn
from table A
LEFT JOIN tableB on tableA.columnname= tableB.columnname

The Left Join Example


Here let us assume that the 'Department' table is first table and placed on the left side.The 'Department' table contains the following columns and their respective data as shown below.

Select DeptID, DeptName
from Employees

DeptID DeptName
1 Finance
2 Development
3 Testing
4 Delivery
5 Human Resources
6 Administration

The Employees table now becomes the second table or goes on the right side. The 'Employees' tables contains the following columns and their respective data as shown below.

Select EmployeeID,EmployeeName,DateOfBirth,DesignationID,DeptID,City,Salary
from Department

EmployeeID EmployeeName DateOfBirth DesignationID DeptID City Salary
1 Richard Hughes 4/23/1945 12:00:00 AM 1 1 New Orleans 950.00
2 Taryn Sinclair 3/22/1980 12:00:00 AM 2 2 San Francisco 800.00
3 Ted Horowitz 3/31/1960 12:00:00 AM 3 4 New York 700.00
4 Jonathan Douglas 7/7/1971 12:00:00 AM 3 4 Salt Lake City 600.00
5 Miranda Leigh 7/19/1983 12:00:00 AM 7 3 New York 500.00
6 Jana Rae 9/23/1976 12:00:00 AM 8 2 Houston 400.00
7 Lita Rosanna 9/14/1982 12:00:00 AM 4 6 Long Island 650.00
8 Colin Flooks 12/29/1988 12:00:00 AM 4 2 Salt Lake City 600.00
9 Anthony Frank 2/22/1988 12:00:00 AM 9 3 San Francisco 750.00
10 Stephanie Lynn 7/30/1979 12:00:00 AM 8 2 Salt Lake City 450.00
11 Jack Martin 8/25/1985 12:00:00 AM NULL NULL Austin 500.00

Select A.DeptID,A.DeptName, B.EmployeeID,B.EmployeeName,B.DateOfBirth,
B.DesignationID,B.City,B.Salary
from Department A
Left Join Employees B on A.DeptID=B.DeptID

DeptID DeptName EmployeeID EmployeeName DateOfBirth DesignationID City Salary
1 Finance 1 Richard Hughes 4/23/1945 12:00:00 AM 1 New Orleans 950.00
2 Development 2 Taryn Sinclair 3/22/1980 12:00:00 AM 2 San Francisco 800.00
2 Development 6 Jana Rae 9/23/1976 12:00:00 AM 8 Houston 400.00
2 Development 8 Colin Flooks 12/29/1988 12:00:00 AM 4 Salt Lake City 600.00
2 Development 10 Stephanie Lynn 7/30/1979 12:00:00 AM 8 Salt Lake City 450.00
3 Testing 5 Miranda Leigh 7/19/1983 12:00:00 AM 7 New York 500.00
3 Testing 9 Anthony Frank 2/22/1988 12:00:00 AM 9 San Francisco 750.00
4 Delivery 3 Ted Horowitz 3/31/1960 12:00:00 AM 3 New York 700.00
4 Delivery 4 Jonathan Douglas 7/7/1971 12:00:00 AM 3 Salt Lake City 600.00
5 Human Resources NULL NULL NULL NULL NULL NULL
6 Administration 7 Lita Rosanna 9/14/1982 12:00:00 AM 4 Long Island 650.00

Note: The Left Join occurs on the common column DeptID which is present in both the tables. Here each record in the first table is taken and checked against every record in the 'Employees' table to find a matching record. If so then it is selected and displayed else null values are returned for the said record. You will notice that department - Human Resources has no employees under it so the corresponding columns of the 'Employees' table display null values.


Next Chapter »