17.05 - Outer Join Relational Algebra - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

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

where:

Syntax element … Specifies …
projection the result of applying a PROJECT operator that extracts one or more attributes from one or more relations.

This 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.

Note that 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.

This defines a join, specifically, 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 )

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.
UNION ALL 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.