Correlated Subqueries | SQL SELECT Statements | Teradata Vantage - 17.05 - Correlated Subqueries - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

A subquery is correlated when it references columns of outer tables in an enclosing or containing outer query.

The expression correlated subquery comes from the explicit requirement for the use of correlation names in any correlated subquery in which the same table is referenced in both the internal and external query.

The reference from the inner query to its containing query is shown in this illustration.



When the inner table references a foreign key column of a different table, the column must be fully qualified in the interior WHERE clause.

The effect of a correlated subquery is to provide an implicit loop function within any standard SQL DML statement, including ABORT, DELETE, INSERT, and UPDATE.

A reference to variables in an outer expression by a variable in an inner expression is called an outer reference.

IF a scalar subquery references tables in its containing query that are … THEN it is said to be …
not referenced in its own FROM clause correlated.
referenced in its own FROM clause noncorrelated.

If a column reference is made from an inner query to an outer query, then it must be fully qualified with the appropriate table name from the FROM clause of the outer query.

ANSI Compliance

Correlated subqueries are ANSI SQL:2011-compliant.