Example: FROM Clause Used for a Self-Join - 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 following result is returned: