Editing Join (SQL) (section)

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