FAQ: What is a Left Outer Join?

FAQ: What is a Left Outer Join?

Overview
A left outer join (or left join) does not require that there be matching records for each value in the key value of the source (left) table. Each row in the left table appears in the results, regardless of whether there are matches in the right table.


Example
A user wants to perform a left outer join on the following two tables using DepartmentID as a key:

Employee table:

Name

DepartmentID

Role

Dave Smith

001

Product Marketing Manager

Julie Jones

002

Software Engineer

Scott Tanner

001

Director of Demand Gen

Ted Connors

002

Software Engineer

Margaret Lane

001

VP of Marketing

Mary Martin

004

Receptionist

Department table:

Name

DepartmentID

Marketing

001

Engineering

002

Accounting

003


For the example tables, a left join produces the following result table:

Employee.Name

Employee.DepartmentID

Employee.Role

Department.Name

Department.DepartmentID

Dave Smith

001

Product Marketing Manager

Marketing

001

Julie Jones

002

Software Engineer

Engineering

002

Scott Tanner

001

Director of Demand Gen

Marketing

001

Ted Connors

002

Software Engineer

Engineering

002

Margaret Lane

001

VP of Marketing

Marketing

001

Mary Martin

004

Receptionist

NULL

NULL

Notes: In this left join, the Mary Martin row has been added to the result, since her record in the Employee table does contain an entry for the DepartmentID. However, since there are no corresponding values in the Department table, the corresponding fields in the result table are NULL values.


keywords: join, left outer join