FROM Clause Examples | SQL SELECT Statements | Teradata Vantage - 17.05 - Example: FROM Clause Used for a Self-Join - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

The following statement returns employees who have more years of work experience than their department managers:

     SELECT workers.name, workers.yrs_exp, workers.dept_no,
            managers.name, managers.yrs_exp
     FROM employee AS workers, employee AS managers
     WHERE managers.dept_no = workers.dept_no
     AND   UPPER (managers.jobtitle) IN ('MANAGER' OR 'VICE PRES')
     AND   workers.yrs_exp > managers.yrs_exp;

The FROM clause in the preceding statement enables the employee table to be processed as though it were two identical tables: one named workers and the other named managers.

As in a normal join operation, the WHERE clause defines the conditions of the join, establishing dept_no as the column whose values are common to both tables.

The statement is processed by first selecting managers rows that contain a value of either ‘MANAGER’ or ‘VICE PRES’ in the jobtitle column. These rows are then joined to the workers rows using a merge join operation with this join condition:
  • A workers row must contain a dept_no value that matches the dept_no value in a managers row.
  • The matching workers row must also contain a yrs_exp value that is greater than the yrs_exp value in the managers row.

The following result is returned:

name

----

yrs_exp

------

dept_no

-------

name

----

yrs_exp

------

Greene W 15 100 Jones M 13
Carter J 20 200 Watson L  8
Aguilar J 11 600 Regan R 10
Leidner P 13 300 Phan A 12
Ressel S 25 300 Phan A 12