Self-Join | SQL Joins | Teradata Vantage - Self-Join - Analytics Database - Teradata Vantage

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
ft:locale
en-US
ft:lastEdition
2024-12-13
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
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