Editing Join (SQL) (section)

===Full outer join===
Conceptually, a '''full outer join''' combines the effect of applying both left and right outer joins. Where rows in the full outer joined tables do not match, the result set will have NULL values for every column of the table that lacks a matching row. For those rows that do match, a single row will be produced in the result set (containing columns populated from both tables).

For example, this allows us to see each employee who is in a department and each department that has an employee, but also see each employee who is not part of a department and each department which doesn't have an employee.

Example of a full outer join (the '''<code>OUTER</code>''' keyword is optional):

<syntaxhighlight lang=sql>
SELECT *
FROM employee FULL OUTER JOIN department
  ON employee.DepartmentID = department.DepartmentID;
</syntaxhighlight>

{| class="wikitable" style="text-align:center"
! Employee.LastName !! Employee.DepartmentID !! Department.DepartmentName !! Department.DepartmentID
|-
| Smith || 34 || Clerical || 34
|-
| Jones || 33 || Engineering || 33
|-
| Robinson || 34 || Clerical || 34
|-
| ''Williams'' || {{null result}} || {{null result}} || {{null result}}
|-
| Heisenberg || 33 || Engineering || 33
|-
| Rafferty || 31 || Sales || 31
|-
| {{null result}} || {{null result}} || ''Marketing'' || ''35''
|}

Some database systems do not support the full outer join functionality directly, but they can emulate it through the use of an inner join and UNION ALL selects of the "single table rows" from left and right tables respectively. The same example can appear as follows:
<syntaxhighlight lang=sql>
SELECT employee.LastName, employee.DepartmentID,
       department.DepartmentName, department.DepartmentID
FROM employee
INNER JOIN department ON employee.DepartmentID = department.DepartmentID

UNION ALL

SELECT employee.LastName, employee.DepartmentID,
       cast(NULL as varchar(20)), cast(NULL as integer)
FROM employee
WHERE NOT EXISTS (
    SELECT * FROM department
             WHERE employee.DepartmentID = department.DepartmentID)

UNION ALL

SELECT cast(NULL as varchar(20)), cast(NULL as integer),
       department.DepartmentName, department.DepartmentID
FROM department
WHERE NOT EXISTS (
    SELECT * FROM employee
             WHERE employee.DepartmentID = department.DepartmentID)
</syntaxhighlight>

Another approach could be UNION ALL of left outer join and right outer join MINUS inner join.