16.20 - Subqueries - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Fundamentals

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Language
English (United States)
Last Update
2019-05-02
dita:mapPath
zce1519094756513.ditamap
dita:ditavalPath
TD_DBS_16_20_Update1.ditaval

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 Teradata Vantage™ SQL Data Manipulation Language , B035-1146 .