16.20 - Example: Creating a View that Defines a Self-Join with a Table - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Syntax and Examples

Teradata Database
Teradata Vantage NewSQL Engine
Release Number
March 2019
Content Type
Programming Reference
Publication ID
English (United States)
Last Update

The following request creates a view that allows a personnel executive to keep track of employees who have more experience on the job than their supervisors. This request defines a self-join of the employee table. The correlation names workers and managers, created in the FROM clause, see the two temporary tables participating in the self-join.

    CREATE VIEW emp_info (workername,workeryrsexp,department, 
                          managername,manageryrsexp) AS
     SELECT workers.name, workers.yrsexp, workers.deptno, 
            managers.name, managers.yrsexp
     FROM employee AS workers, employee AS managers 
     WHERE workers.deptno = managers.deptno 
     AND managers.jobtitle IN ('Manager', 'Vice Pres') 
     AND workers.yrsexp > managers.yrsexp;