Editing Relational algebra (section)

=== Outer joins ===
{{See also|Join (SQL)#Outer join}}
Whereas the result of a join (or inner join) consists of tuples formed by combining matching tuples in the two operands, an outer join contains those tuples and additionally some tuples formed by extending an unmatched tuple in one of the operands by "fill" values for each of the attributes of the other operand. Outer joins are not considered part of the classical relational algebra discussed so far.<ref name="O'NeilO'Neil2001">{{cite book|author1=Patrick O'Neil|author2=Elizabeth O'Neil|author2-link=Elizabeth O'Neil|title=Database: Principles, Programming, and Performance, Second Edition|url=https://books.google.com/books?id=UXh4qTpmO8QC&pg=PA120|year=2001|publisher=Morgan Kaufmann|isbn=978-1-55860-438-4|page=120}}</ref>

The operators defined in this section assume the existence of a ''null'' value, ''ω'', which we do not define, to be used for the fill values; in practice this corresponds to the [[Null (SQL)|NULL]] in SQL. In order to make subsequent selection operations on the resulting table meaningful, a semantic meaning needs to be assigned to nulls; in Codd's approach the propositional logic used by the selection is [[Null (SQL)#Comparisons with NULL and the three-valued logic .283VL.29|extended to a three-valued logic]], although we elide those details in this article.

Three outer join operators are defined: left outer join, right outer join, and full outer join.  (The word "outer" is sometimes omitted.)

==== Left outer join ====

The left outer join (⟕) is written as ''R'' ⟕ ''S'' where ''R'' and ''S'' are [[relation (database)|relation]]s.{{efn|In [[Unicode]], the Left outer join symbol is ⟕ (U+27D5).}} The result of the left outer join is the set of all combinations of tuples in ''R'' and ''S'' that are equal on their common attribute names, in addition (loosely speaking) to tuples in ''R'' that have no matching tuples in ''S''.{{citation needed|date=April 2022}}

For an example consider the tables ''Employee'' and ''Dept'' and their  left outer join:

{{col-begin|width=auto; margin:0.5em auto}}
{{col-break}}
{| class="wikitable"
 |+           ''Employee''
 |-
 ! Name   !! EmpId !! DeptName
 |-
 | Harry  || 3415  || Finance
 |-
 | Sally  || 2241  || Sales
 |-
 | George || 3401  || Finance
 |-
 | Harriet || 2202 || Sales
 |-
 | Tim || 1123 || Executive
|}
{{col-break|gap=2em}}
{| class="wikitable"
 |+           ''Dept''
 |-
 ! DeptName   !! Manager
 |-
 | Sales  || Harriet
 |-
 | Production || Charles
|}
{{col-break|gap=2em}}
{| class="wikitable"
 |+  ''Employee'' ⟕  ''Dept''
 |-
 ! Name   !! EmpId !! DeptName !! Manager
 |-
 | Harry  || 3415  || Finance || ω
 |-
 | Sally  || 2241   || Sales || Harriet
 |-
 | George || 3401  || Finance || ω
 |-
 | Harriet || 2202 || Sales || Harriet
 |-
 | Tim || 1123 || Executive || ω
|}
{{col-end}}

In the resulting relation, tuples in ''S'' which have no common values in common attribute names with tuples in ''R'' take a ''null'' value, ''ω''.

Since there are no tuples in ''Dept'' with a ''DeptName'' of ''Finance'' or ''Executive'', ''ω''s occur in the resulting relation where tuples in ''Employee'' have a ''DeptName'' of ''Finance'' or ''Executive''.

Let ''r''<sub>1</sub>, ''r''<sub>2</sub>, ..., ''r''<sub>''n''</sub> be the attributes of the relation ''R'' and let {(''ω'', ..., ''ω'')} be the singleton
relation on the attributes that are ''unique'' to the relation ''S'' (those that are not attributes of ''R''). Then the left outer join can be described in terms of the natural join (and hence using basic operators) as follows:

:<math>(R \bowtie S) \cup ((R - \pi_{r_1, r_2, \dots, r_n}(R \bowtie S)) \times \{(\omega, \dots, \omega)\})</math>

==== Right outer join ====
The right outer join (⟖) behaves almost identically to the left outer join, but the roles of the tables are switched.

The right outer join of [[relation (database)|relation]]s ''R'' and ''S'' is written as ''R'' ⟖ ''S''.{{efn|In [[Unicode]], the Right outer join symbol is ⟖ (U+27D6).}} The result of the right outer join is the set of all combinations of tuples in ''R'' and ''S'' that are equal on their common attribute names, in addition to tuples in ''S'' that have no matching tuples in ''R''.{{citation needed|date=April 2022}}

For example, consider the tables ''Employee'' and ''Dept'' and their right outer join:

{{col-begin|width=auto; margin:0.5em auto}}
{{col-break}}
{| class="wikitable"
 |+           ''Employee''
 |-
 ! Name   !! EmpId !! DeptName
 |-
 | Harry  || 3415  || Finance
 |-
 | Sally  || 2241  || Sales
 |-
 | George || 3401  || Finance
 |-
 | Harriet || 2202 || Sales
 |-
 | Tim || 1123 || Executive
|}
{{col-break|gap=2em}}
{| class="wikitable"
 |+           ''Dept''
 |-
 ! DeptName   !! Manager
 |-
 | Sales  || Harriet
 |-
 | Production || Charles
|}
{{col-break|gap=2em}}
{| class="wikitable"
 |+  ''Employee'' ⟖  ''Dept''
 |-
 ! Name   !! EmpId !! DeptName !! Manager
 |-
 | Sally  || 2241   || Sales  || Harriet
 |-
 | Harriet || 2202 || Sales  || Harriet
 |-
 | ω || ω || Production  || Charles
|}
{{col-end}}

In the resulting relation, tuples in ''R'' which have no common values in common attribute names with tuples in ''S'' take a ''null'' value, ''ω''.

Since there are no tuples in ''Employee'' with a ''DeptName'' of ''Production'', ''ω''s occur in the Name and EmpId attributes of the resulting relation where tuples in ''Dept'' had ''DeptName'' of ''Production''.

Let ''s''<sub>1</sub>, ''s''<sub>2</sub>, ..., ''s''<sub>''n''</sub> be the attributes of the relation ''S'' and let {(''ω'', ..., ''ω'')} be the singleton
relation on the attributes that are ''unique'' to the relation ''R'' (those that are not attributes of ''S''). Then, as with the left outer join, the right outer join can be simulated using the natural join as follows:

:<math>(R \bowtie S) \cup (\{(\omega, \dots, \omega)\} \times (S - \pi_{s_1, s_2, \dots, s_n}(R \bowtie S)))</math>

==== Full outer join ====

The '''outer join''' (⟗) or '''full outer join''' in effect combines the results of the left and right outer joins.

The full outer join is written as ''R'' ⟗ ''S'' where ''R'' and ''S'' are [[relation (database)|relation]]s.{{efn|In [[Unicode]], the Full Outer join symbol is ⟗ (U+27D7).}} The result of the full outer join is the set of all combinations of tuples in ''R'' and ''S'' that are equal on their common attribute names, in addition to tuples in ''S'' that have no matching tuples in ''R'' and tuples in ''R'' that have no matching tuples in ''S'' in their common attribute names.{{citation needed|date=April 2022}}

For an example consider the tables ''Employee'' and ''Dept'' and their full outer join:

{{col-begin|width=auto; margin:0.5em auto}}
{{col-break}}
{| class="wikitable"
 |+           ''Employee''
 |-
 ! Name   !! EmpId !! DeptName
 |-
 | Harry  || 3415  || Finance
 |-
 | Sally  || 2241  || Sales
 |-
 | George || 3401  || Finance
 |-
 | Harriet || 2202 || Sales
 |-
 | Tim || 1123 || Executive
|}
{{col-break|gap=2em}}
{| class="wikitable"
 |+           ''Dept''
 |-
 ! DeptName   !! Manager
 |-
 | Sales  || Harriet
 |-
 | Production || Charles
|}
{{col-break|gap=2em}}
{| class="wikitable"
 |+  ''Employee'' ⟗  ''Dept''
 |-
 ! Name   !! EmpId !! DeptName !! Manager
 |-
 | Harry  || 3415  || Finance || ω
 |-
 | Sally  || 2241   || Sales || Harriet
 |-
 | George || 3401  || Finance || ω
 |-
 | Harriet || 2202 || Sales || Harriet
 |-
 | Tim || 1123 || Executive || ω
 |-
 | ω || ω || Production  || Charles
|}
{{col-end}}

In the resulting relation, tuples in ''R'' which have no common values in common attribute names with tuples in ''S'' take a ''null'' value, ''ω''. Tuples in ''S'' which have no common values in common attribute names with tuples in ''R'' also take a ''null'' value, ''ω''.

The full outer join can be simulated using the left and right outer joins (and hence the natural join and set union) as follows:

:''R'' ⟗ ''S'' = (''R'' ⟕ ''S'') &cup; (''R'' ⟖ ''S'')