Editing Join (SQL) (section)

==Outer join==
The joined table retains each row—even if no other matching row exists. Outer joins subdivide further into left outer joins, right outer joins, and full outer joins, depending on which table's rows are retained: left, right, or both (in this case ''left'' and ''right'' refer to the two sides of the <code>JOIN</code> keyword).  Like [[#Inner join|inner joins]], one can further sub-categorize all types of outer joins as [[#Equi-join|equi-joins]], [[#Natural join|natural joins]], <code>'''ON''' ''&lt;predicate&gt;''</code> ([[Relational algebra#θ-join and equijoin|''&theta;''-join]]), etc.<ref>{{cite book |title=Database System Concepts |section=Section 4.10.2: Join Types and Conditions |page=166 |last1=Silberschatz |first1=Abraham |author-link1=Abraham Silberschatz|author2-link=Henry F. Korth |last2=Korth |first2=Hank |last3=Sudarshan |first3=S. |edition=4th |year=2002 |publisher=McGraw-Hill |isbn=0072283637}}</ref>

No implicit join-notation for outer joins exists in standard SQL.
[[File:SQL Join - 01 A Left Join B.svg|alt=A Venn diagram showing the left circle and overlapping portion filled.|thumb|A Venn diagram representing the left join SQL statement between tables A and B.]]

===Left outer join===
The result of a '''left outer join''' (or simply '''left join''') for tables A and B always contains all rows of the "left" table (A), even if the join-condition does not find any matching row in the "right" table (B). This means that if the <code>ON</code> clause matches 0 (zero) rows in B (for a given row in A), the join will still return a row in the result (for that row)—but with NULL in each column from B. A '''left outer join''' returns all the values from an inner join plus all values in the left table that do not match to the right table, including rows with NULL (empty) values in the link column.

For example, this allows us to find an employee's department, but still shows employees that have not been assigned to a department (contrary to the inner-join example above, where unassigned employees were excluded from the result).

Example of a left outer join (the '''<code>OUTER</code>''' keyword is optional), with the additional result row (compared with the inner join) italicized:

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

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

====Alternative syntaxes====
Oracle supports the deprecated<ref name="deprecated_plus_sign">
[http://www.dba-oracle.com/tips_oracle_left_outer_join.htm Oracle Left Outer Join]</ref> syntax:
<syntaxhighlight lang="sql">
SELECT *
FROM employee, department
WHERE employee.DepartmentID = department.DepartmentID(+)
</syntaxhighlight>
[[Sybase]] supports the syntax ([[Microsoft SQL Server]] deprecated this syntax since version 2000):
<syntaxhighlight lang=tsql>
SELECT *
FROM employee, department
WHERE employee.DepartmentID *= department.DepartmentID
</syntaxhighlight>
[[IBM Informix]] supports the syntax:
<syntaxhighlight lang=sql>
SELECT *
FROM employee, OUTER department
WHERE employee.DepartmentID = department.DepartmentID
</syntaxhighlight>
[[File:SQL Join - 03 A Right Join B.svg|alt=A Venn diagram show the right circle and overlapping portions filled.|thumb|
A Venn diagram representing the right join SQL statement between tables A and B.
]]

===Right outer join===
A '''right outer join''' (or '''right join''') closely resembles a left outer join, except with the treatment of the tables reversed. Every row 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 rows that have no match in B.

A right outer join returns all the values from the right table and matched values from the left table (NULL in the case of no matching join predicate). For example, this allows us to find each employee and his or her department, but still show departments that have no employees.

Below is an example of a right outer join (the '''<code>OUTER</code>''' keyword is optional), with the additional result row italicized:

<syntaxhighlight lang=sql>
SELECT *
FROM employee RIGHT 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
|-
| Heisenberg || 33 || Engineering || 33
|-
| Rafferty || 31 || Sales || 31
|-
| {{null result}} || {{null result}} || ''Marketing'' || ''35''
|}

Right and left outer joins are functionally equivalent. Neither provides any functionality that the other does not, so right and left outer joins may replace each other as long as the table order is switched.
[[File:SQL Join - 05b A Full Join B.svg|alt=A Venn diagram showing the right circle, left circle, and overlapping portion filled.|thumb|A Venn diagram representing the full join SQL statement between tables A and B.]]

===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.