15.10 - Self-Join - Teradata Database

Teradata Database SQL Request and Transaction Processing
prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
User Guide
featnum
B035-1142-151K

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:

 

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: