SQL Left Outer Join

Download SQL for free

Example

A Left Outer Join (also known as a Left Join or Outer Join) is a Join that ensures all rows from the left table are represented; if no matching row from the right table exists, its corresponding fields are NULL.

The following example will select all departments and the first name of employees that work in that department. Departments with no employees are still returned in the results, but will have NULL for the employee name:

SELECT          Departments.Name, Employees.FName
FROM            Departments 
LEFT OUTER JOIN Employees 
ON              Departments.Id = Employees.DepartmentId

This would return the following from the example database:

Departments.NameEmployees.FName
HRJames
HRJohn
HRJohnathon
SalesMichael
TechNULL

So how does this work?

There are two tables in the FROM clause:

IdFNameLNamePhoneNumberManagerIdDepartmentIdSalaryHireDate
1JamesSmith1234567890NULL1100001-01-2002
2JohnJohnson24681012141140023-03-2005
3MichaelWilliams13579111311260012-05-2009
4JohnathonSmith12121212122150024-07-2016

and

IdName
1HR
2Sales
3Tech

First a Cartesian product is created from the two tables giving an intermediate table.
The records that meet the join criteria (Departments.Id = Employees.DepartmentId) are highlighted in bold; these are passed to the next stage of the query.

As this is a LEFT OUTER JOIN all records are returned from the LEFT side of the join (Departments), while any records on the RIGHT side are given a NULL marker if they do not match the join criteria. In the table below this will return Tech with NULL

IdNameIdFNameLNamePhoneNumberManagerIdDepartmentIdSalaryHireDate
1HR1JamesSmith1234567890NULL1100001-01-2002
1HR2JohnJohnson24681012141140023-03-2005
1HR3MichaelWilliams13579111311260012-05-2009
1HR4JohnathonSmith12121212122150024-07-2016
2Sales1JamesSmith1234567890NULL1100001-01-2002
2Sales2JohnJohnson24681012141140023-03-2005
2Sales3MichaelWilliams13579111311260012-05-2009
2Sales4JohnathonSmith12121212122150024-07-2016
3Tech1JamesSmith1234567890NULL1100001-01-2002
3Tech2JohnJohnson24681012141140023-03-2005
3Tech3MichaelWilliams13579111311260012-05-2009
3Tech4JohnathonSmith12121212122150024-07-2016

Finally each expression used within the SELECT clause is evaluated to return our final table:

Departments.NameEmployees.FName
HRJames
HRJohn
SalesRichard
TechNULL
Recursive JOINs