Editing Relational algebra (section)

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