SQL Outer Joins

An outer join does not require each record in the two joined tables to have a matching record in the other table. The joined table retains each record—even if no other matching record exists. Outer joins subdivide further into left outer joins, right outer joins, and full outer joins, depending on which table(s) one retains the rows from (left, right, or both).

Left Outer Join

The result of a left outer join for tables A and B always contains all records of the "left" table (A), even if the join-condition does not find any matching record in the "right" table (B). This means that if the ON clause matches 0 (zero) records in B, the join will still return a row in the result—but with NULL in each column from B.

A left outer join returns all the values from the left table, plus matched values from the right table (or NULL in case of no matching join predicate).

Example of a left outer join:

SELECT *
FROM   employee
     LEFT OUTER JOIN department
        ON employee.DepartmentID = department.DepartmentID

Emp.LName	Emp.DeptID	Dept.DeptName	Dept.DeptID
Jones	33	Engineering	33
Rafferty	31	Sales	31
Robinson	34	Clerical	34
Smith	34	Clerical	34
Jasper	36	NULL	NULL
Steinberg	33	Engineering	33

Right Outer Join

A right outer join closely resembles a left outer join, except with the tables reversed. Every record from the "right" table (B) will appear in the joined table at least once. If no matching row from the "left" table (A) exists, NULL will appear in columns from A for those records that have no match in A.

A right outer join returns all the values from the right table and matched values from the left table (or NULL in case of no matching join predicate).

Example right outer join:

SELECT *
FROM   employee
     RIGHT OUTER JOIN department
        ON employee.DepartmentID = department.DepartmentID

Emp.LName	Emp.DeptID	Dept.DeptName	Dept.DeptID
Smith	34	Clerical	34
Jones	33	Engineering	33
Robinson	34	Clerical	34
Steinberg	33	Engineering	33
Rafferty	31	Sales	31
NULL	NULL	Marketing	35

Full Outer Join

A full outer join combines the results of both left and right outer joins. The joined table will contain all records from both tables, and fill in NULLs for missing matches on either side.

Example full outer join:

SELECT *
FROM   employee
     FULL OUTER JOIN department
        ON employee.DepartmentID = department.DepartmentID

Emp.LName	Emp.DeptID	Dept.DeptName	Dept.DeptID
Smith	34	Clerical	34
Jones	33	Engineering	33
Robinson	34	Clerical	34
Jasper	36	NULL	NULL
Steinberg	33	Engineering	33
Rafferty	31	Sales	31
NULL	NULL	Marketing	35