Self-join Operations and the FROM Clause - Advanced SQL Engine - Teradata Database

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.

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;