16.10 - Subqueries - Teradata Database

Teradata Database SQL Fundamentals

Product
Teradata Database
Release Number
16.10
Published
June 2017
Content Type
Programming Reference
Publication ID
B035-1141-161K
Language
English (United States)
Last Update
2018-04-25

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

   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.

Related Topics

For more information, see SQL Data Manipulation Language , B035-1146 .