FAQ: What is a Right Outer Join?

FAQ: What is a Right Outer Join?

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

Example
A user wants to perform a right 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 right join on the DepartmentID table 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

NULL

NULL

NULL

Accounting

003

Notes: In this right join, the Accounting entry is added. However, since there is no entry in the Employee table for the DepartmentID value, those fields are NULL values in the result set.


keywords: join, right outer join