17.05 - Self-join Operations and the FROM Clause - 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)

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;