15.00 - Subqueries - Teradata Database

Teradata Database SQL Fundamentals

Teradata Database
Programming Reference


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 
      (SELECT MgrNo 
       FROM Department 
       WHERE DeptNo IN
         (SELECT DeptNo 
          FROM Employee 
          WHERE Name = 'Marston A') ) ;

The subqueries that pose the questions leading to the final answer are inverted:

  • 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:

       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

    For more information, see SQL Data Manipulation Language.