Self-Join | Join Planning/Optimization | Teradata Vantage - Self-Join - Analytics Database - Teradata Vantage

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 use a self-join of the employee table.

The rows from employee may look something like this:

To determine the two-way pairings of employees living in the same country, 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: