Self-Join | SQL Joins | Teradata Vantage - Self-Join - Advanced SQL Engine - Teradata Database

SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
qtb1554762060450.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata Vantage™

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 as if it were two tables; one named Workers, the other named Managers. This is accomplished by using table name aliases in the FROM clause.

ANSI calls table aliases correlation names. They are also referred to as 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”).

The WHERE clause establishes the following things:
  • A key to both tables (dept_no)
  • Which employees belong in the managers table (first AND)
  • Which workers and managers should be listed in the tables (second AND)

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

name yrs_exp dept_no name yrs_exp
Greene W 15 100 Jones M 13
Carter J 20 500 Watson L 8
Smith T 10 700 Watson L 8
Aguilar J 11 600 Regan R 10
Leidner P 13 300 Phan A 12
Russell S 25 300 Phan A 12