Correlated Subqueries | SQL SELECT Statements | Teradata Vantage - Correlated Subqueries - Analytics Database - Teradata Vantage

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
Teradata Vantage™

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.