FROM Clause Examples | SQL SELECT Statements | Teradata Vantage - Example: FROM Clause Used for a 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
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
Teradata Vantage™

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