Subqueries | SQL Fundamentals | Teradata Vantage - Subqueries - Advanced SQL Engine - Teradata Database

Example: Three Level Subqueries

The following subqueries, nested to three levels, answer the question “Who manages the manager of Marston?”

SELECT Name
FROM Employee
WHERE EmpNo IN
   (SELECT MgrNo
    FROM Department
    WHERE DeptNo IN
      (SELECT DeptNo
       FROM Employee
       WHERE Name = 'Marston A') ) ;

The result table looks like the following:

Name
--------
Watson L

This result can be obtained using only two levels of subquery, as the following example shows.

SELECT Name
FROM Employee
WHERE EmpNo IN
 (SELECT MgrNo
  FROM Department, Employee
  WHERE Employee.Name = 'Marston A'
  AND Department.DeptNo = Employee.DeptNo) ;

In this example, the second subquery defines a join of Employee and Department tables.

This result could also be obtained using a one-level query that uses correlation names, as the following example shows.

SELECT M.Name
FROM Employee M, Department D, Employee E
WHERE M.EmpNo = D.MgrNo AND
      E.Name = 'Marston A' AND
      D.DeptNo = E.DeptNo;

In some cases, as in the preceding example, the choice is a style preference. In other cases, correct execution of the query may require a subquery.