Outer Joins

Introduction

An outer join is an operation that creates a list of records of two (or more) entities as follows:

• The database engine would look for values in records from one table that match values in records of the other table
• Any value from a record of one table and that do not match values from the records of the other table would still be included in the result but would be considered null

Let’s consider the following list of departments of a company:

Let’s consider the following list of employees of a company:

The outer join operation is performed using the OUTER JOIN operator. It comes in various options, which precede the OUTER JOIN expression with a certain keyword:

• LEFT OUTER JOIN: The result will include all records from the first entity (referred to as the left entity or left table) and find the records of the other entity (referred to as the right entity or right table) that match a value from the left entity. The records of the left entity that do not have a matching value from the right entity would be considered null and ignored. Here is an example of SQL code from the above tables:

  SELECT Employees.EmployeeNumber,
         Employees.FirstName,
         Employees.LastName,
         Departments.Department,
         Employees.Title
  FROM Departments
  LEFT OUTER JOIN Employees ON Departments.Code = Employees.DepartmentCode;

  This would produce:

  

• RIGHT OUTER JOIN: The result will include all records of the right entity. Then the database engine verifies the records of the right entity that are represented in the left entity. Here is an example of code that creates a right outer join from the above tables:

  SELECT Employees.EmployeeNumber,
         Employees.FirstName,
         Employees.LastName,
         Departments.Department,
         Employees.Title
  FROM Departments
  RIGHT OUTER JOIN Employees ON Departments.Code = Employees.DepartmentCode;
  

  This would produce: