Outer Join Relational Algebra - Analytics Database - Teradata Vantage

The relational algebra and the relational calculus are two different, but equivalent, formal languages for manipulating relations. Algebra is procedural, for internal representations of queries that can be manipulated by query optimizers and database managers, while the calculus is nonprocedural, providing a foundation for user-malleable query languages.

The basic query SELECT FROM WHERE, which is the SQL dialect of the generalized nonprocedural relational calculus for a query, can be restated in terms of relational algebra as follows.

projection ( restriction ( product ) )

projection

The result of applying a PROJECT operator that extracts one or more attributes from one or more relations.

projection defines an SQL SELECT FROM. Projection selects columns from tables.

restriction

The result of applying a RESTRICT (or SELECT) operator that extracts one or more tuples from the projection.

The SELECT of relational algebra is different than the SELECT statement of SQL, which essentially performs both a PROJECT operation and a RESTRICT operation.

restriction defines the WHERE, ON, , and QUALIFY clauses of an SQL SELECT statement. restriction selects qualified rows from tables. When a join condition exists, restriction selects qualified rows from the intermediate results table produced by the join.

product

The result of applying a PRODUCT operator that builds all possible combinations of tuples, one from each specified relation.

product defines an inner join.

For clarity of presentation, restate the original expression as projection (inner join).

Relational Algebra for the Outer Join

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 UNION ALL extension )

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.

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.

Because UNION ALL permits duplicates, it is not a relational operator in the strict sense of the term.