FAQ: What is a Full Outer Join?

FAQ: What is a Full Outer Join?

Overview
A full outer join combines the effects of a left join and a right join. If there is a match between the key values, a row is written in the result.If there is no match for a key value that appears in either table, a single record is written to the result, with NULL values inserted for the fields from the other table.

Example
A user wants to perform a full 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

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

NULL

NULL

NULL

Accounting

003

Notes:

  • Any duplicated rows between joining from left-to-right and from right-to-left are removed from the results.

keywords: full outer join, join