16.10 - Self-Join - Teradata Database

Teradata Database SQL Request and Transaction Processing

Teradata Database
June 2017
Programming Reference
User Guide

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:

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