Self-Join | Join Planning/Optimization | Teradata Vantage - 17.10 - Self-Join - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - SQL Request and Transaction Processing

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
User Guide
Publication ID
B035-1142-171K
Language
English (United States)

Self-Join Example

If a join is defined as a request in which rows are retrieved from more than one table, a self-join can loosely be defined as a request in which rows are retrieved from a single table that is redefined as two tables by using correlation names to appear as if there are 2 copies of the same table under different names.

Suppose you want to determine all two-way pairings of employees who live in the same country. To determine this, you would use a self-join of the employee table.

The rows from employee might look something like this:

employee
emp_num name country dept_num
113722 Lopes United States 115
225985 Ghazal United States 115
577321 Korlapati United States 115
783904 Ramesh India 378
799106 Manjula India 378
942764 Ono Japan 915

To determine the two-way pairings of employees living in the same country, you would submit the following self-join request:

SELECT e.emp_num, e.name, f.emp_num, f.name, e.country
FROM employee AS e, employee AS f
WHERE e.country = f.country
AND   e.emp_num < f.emp_num
ORDER BY e.emp_num, f.emp_num;

By specifying two different correlation names for employee, you are able to join the table to itself. Note that the condition e.country = f.country pairs only those employees who live in the same country, while the condition e.emp_num < f.emp_num pairs only employees who have different employee numbers.

The request produces the following result table:

employee after self-join on country
emp_num name emp_num name country
113722 Lopes 225085 Ghazal United States
113722 Lopes 577321 Korlapati United States
225085 Ghazal 577321 Korlapati United States
783004 Ramesh 799106 Manjula India