Self-join Operations and the FROM Clause - Teradata Database - Teradata Vantage NewSQL Engine

SQL Data Manipulation Language

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Language
English (United States)
Last Update
2019-05-03
dita:mapPath
fbo1512081269404.ditamap
dita:ditavalPath
TD_DBS_16_20_Update1.ditaval
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata Vantage™

During a self-join operation, related data selected from different rows of the same table is combined and returned as a single row. The temporary table names that are defined using the FROM clause qualify different references to the same table columns.

The following table lists the effects of column references on self-join processing. These rules apply only to cases where a single aliasing FROM clause is supplied.
Columns Referenced Type of Reference Self-join
All Unqualified (the preceding table name or correlation name is omitted) Not performed.
Qualified (but the qualifiers reference only the correlation name)
Some, but not all. Qualified and the qualifiers reference only the correlation name
Qualified and the qualifiers reference only the permanent table name Moot because no assumptions can be made about the owner of the unqualified columns. The self-join not performed.

When a FROM clause references both a permanent table name and a correlation name, a self-join can be performed depending on how the column references are qualified.

For a successful self-join operation, column references must be fully qualified (that is, specified in the form table_name.column_name) and the qualifiers should involve both the permanent and the correlation table names.

After a correlation name is declared, any subsequent reference to the base table name causes a new instance of the table to be used. The result of the SELECT can be a Cartesian self-join.

See Example: FROM Clause Used for a Self-Join where table_1 is given the correlation name t. The subsequent specification of table_1 in the WHERE clause then causes the statement to perform a Cartesian product.

     SELECT *
     FROM table_1 AS t
     WHERE table_1.column_1 = 2;