Outer Join Relational Algebra - Analytics Database - Teradata Vantage

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
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.