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

Teradata Vantage™ - SQL Fundamentals

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1141-171K
Language
English (United States)

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 Information

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