Subqueries are nested SELECT statements. They can be used to ask a series of questions to arrive at a single answer.
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 third subquery asks the Employee table for the number of Marston’s department.
- The second subquery asks the Department table for the employee number (MgrNo) of the manager associated with this department number.
- The first subquery asks the Employee table for the name of the employee associated with this employee number (MgrNo).
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.
For more information, see Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.