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.

Joins in SQL Server

SQL server supports following types of joins to retrieve the data from more than one table.

1. Inner join
2. Left outer join
3. Right outer join
4. Self-join
5. Cross join

Inner join: This join is used to retrieve the data which are matched into both tables. For example, if table1 have 10 records and table2 have 8 records and only 3 records are matched with each other using condition then only 3 records will return.

Left outer join: This join is used when need to retrieve all data from the left side table and matched data from right side table. For example table1 have 10 records and table2 have 8 records and only 3 records are matched according to condition then left outer join returns 10 records.

Right outer join: This join is used when need to retrieve all data from the right side table and matched data from left side table. For example table1 have 10 records and table2 have 8 records and only 3 records are matched according to condition then right outer join returns 8 records.


Self-join: When we use same table on both side of join then it is called self-join.


Cross join: This join is used to retrieve the cross joined data. Means every record in table1 will join with all the records of table2. For example, If table1 have 10 records and table2 have 8 records then this join will return 80 records.