The outer join merges the result of an inner join with the remaining tuples in one (or both) of the joined relations that do not share commonality with the tuples of the inner join result table.
The outer join can be expressed in relational algebra as follows:
projection ( inner_join ∪ extension )
where:
Syntax element … |
Specifies … |
projection |
the result of applying a PROJECT operator that extracts one or more attributes from one or more relations. |
inner_join |
the result of applying the RESTRICT (or SELECT) operator to the PRODUCT of the relations in the projection. |
∪ |
the UNION ALL operator. Because UNION ALL permits duplicates, it is not a relational operator in the strict sense of the term. |
extension |
the complement of the result of applying the RESTRICT operator to the PRODUCT of the relations in the projection. Depending on how the query is stated, extension can refer either to the excluded tuples from the left table, the right table, or both. These are, respectively, left, right, and full outer joins. |