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