SELECT Subqueries - Teradata Database

SELECT Subqueries

A subquery is a SELECT expression that is nested within another SQL statement or expression. You can specify SELECT expressions as subqueries in a main query, an outer query, or another subquery for these DML statements:

ABORT (see “ABORT” on page 277)

DELETE (see “DELETE” on page 321)

INSERT (see “INSERT/INSERT … SELECT” on page 347)

MERGE (see “MERGE” on page 405)

ROLLBACK (see “ROLLBACK” on page 462)

SELECT

UPDATE (see “UPDATE” on page 468)

The upsert form of UPDATE does not support subqueries as WHERE clause conditions. See “UPDATE (Upsert Form)” on page 495 for more information about the upsert form of the UPDATE statement.

The syntax rules require that if there are any subqueries in the statement, then any tables referenced in the SELECT must be specified in a FROM clause, either of the subquery itself or of some outer query that contains the subquery.

Correlated subqueries are another category of subquery. A subquery is correlated when it references columns of outer tables in an enclosing or containing, outer query. Correlated subqueries provide an implicit loop function within any standard SQL DML statement.

See “Correlated Subqueries” on page 116 for details on correlated subqueries and their use.

Also see “Example 2: SELECT Statements With a Correlated Subquery” on page 22 for simple examples of correlated subqueries.

Scalar subqueries are another category of subquery that can be coded either as a correlated subquery or as a noncorrelated subquery. A correlated scalar subquery returns a single value for each row of its correlated outer table set. A noncorrelated scalar subquery returns a single value to its containing query. For information about scalar subqueries, see “Scalar Subqueries” on page 124.