Full outer join

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

Some database systems do not support this functionality, but it can be emulated through the use of left and right outer joins and unions (see below).

Example full outer join (ANSI 92 standard syntax):

SELECT * 
FROM employee
     FULL OUTER JOIN
     department
       ON employee.DepartmentID = department.DepartmentID
+-----------+--------------+----------------+--------------+
| LastName  | DepartmentID | DepartmentName | DepartmentID |
+-----------+--------------+----------------+--------------+
| 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 |
+-----------+--------------+----------------+--------------+
The same example, for use on databases that do not support FULL OUTER JOIN:

SELECT
     employee.LastName,
     employee.DepartmentID, 
     department.DepartmentName,
     department.DepartmentID
FROM employee
     LEFT JOIN
     department
       ON employee.DepartmentID = department.DepartmentID
UNION
SELECT
     employee.LastName,
     employee.DepartmentID, 
     department.DepartmentName,
     department.DepartmentID
FROM employee
     RIGHT JOIN
     department
       ON employee.DepartmentID = department.DepartmentID
       WHERE employee.DepartmentID IS NULL