Self-Join | SQL Joins | VantageCloud Lake - Self-Join - Teradata VantageCloud Lake

A self-join combines the information from two or more rows of the same table into a single result row, effectively joining the table with itself.

Example: Self-Join

For example, the following query asks the names of employees who have more years of experience than their managers:

     SELECT workers.name, workers.yrs_exp, workers.dept_no,
      managers.name, managers.yrsexp
     FROM employee AS workers, employee AS managers
     WHERE managers.dept_no = workers.dept_no
     AND   managers.job_title IN ('Manager', 'Vice Pres')
     AND   workers.yrs_exp > managers.yrs_exp;

The operation treats the employee table like two tables, one named Workers and one named Managers. This is accomplished by using table name aliases in the FROM clause.

The ANSI term for table aliases is correlation names. Aliases are also called range variables.

Because each of these fictitious tables has the same columns (name, yrs_exp, and dept_no) each column name must be qualified with the alias name of its table as defined in the FROM clause (for example, workers.dept_no).

A possible result of this self-join is as follows: