Left Outer Join

Left outer join is used to retrieve all records from left side table and matched data from right side of table. If you included any column in select clause from right side of table then SQL server will display NULL value when right side of table does not have any matching record with left side table.

Syntax:

Select column1[, column2 …]

from Table1
     Left [outer] join Table2 on Table1.Column1 = Table2.Column4 [ and Table1.Column6 = Table2.ColumnN]

Let’s take an example to understand left outer join.

We have two tables Employee and Projects. Employee table have 10 employee records and Project table have 5 projects information.

Out of 10 employees, 2 employees are not allocated into any project. So when we execute the query, it will return 10 records.

In result pane, you can see 2 employee records which have NULL value for project information. (These two employees are not allocated into any project).

Query syntax:

Select *

from EmployeeMaster

      left join ProjectMaster on EmployeeMaster.ProjectId = ProjectMaster.ProjectId

Result:

EmployeeID	EmployeeName	ProjectID	ProjectID	ProjectName
1	Emp1	1	1	TestProject1
2	Emp2	1	1	TestProject1
3	Emp3	2	2	TestProject2
4	Emp4	NULL	NULL	NULL
5	Emp5	3	3	TestProject3
6	Emp6	3	3	TestProject3
7	Emp7	NULL	NULL	NULL
8	Emp8	4	4	TestProject4
9	Emp9	4	4	TestProject4
10	Emp10	4	4	TestProject4

Don’t confuse between “Left outer join” and “Left Join”. Both joins are same. “Outer” keyword is optional.