Using Correlated Subqueries - Teradata Vantage

Teradata® VantageCloud Lake
Deployment
VantageCloud
Edition
Lake
Product
Teradata Vantage
Published
January 2023
Language
English (United States)
Last Update
2024-04-03
dita:mapPath
phg1621910019905.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
phg1621910019905
The rules and restrictions are:
  • Correlated subqueries can specify combinations of equality and inequality between the tables of an outer query and the tables of a subquery.

    For example, this SELECT statement specifies an equality condition in its subquery and specifies an inequality condition in the main query:

          SELECT *
      FROM table_1
      WHERE x < ALL (SELECT y
                     FROM table_2
                     WHERE table_2.n = table_1.n);
  • You can nest correlated subqueries to a depth of 64.

    For example, this SELECT statement specifies three levels of depth:

          SELECT *
      FROM table_1
      WHERE x = (SELECT y
                 FROM table_2
                 WHERE table_2.n = table_1.n
                 AND t2.m = (SELECT y
                             FROM table_3
                             WHERE table_3.n = table_2.m
                             AND   table_1.l = table_3.l));
  • A correlated subquery can reference the same table as a table specified in the FROM clause of an outer query.

    To differentiate between the references to the inner and outer tables, rename one with a correlation name.

    In the first example, the outer table is aliased, while in the second example, the inner table is aliased.

    Also, in the first example, the outer reference is a.n, while in the second example, the outer reference is table_1.n.

          SELECT *
      FROM table_1 AS a
      WHERE x < (SELECT AVG(table_1.x)
                 FROM table_1
                 WHERE table_1.n = a.n);

      SELECT *
      FROM table_1
      WHERE x < (SELECT AVG(a.x)
                 FROM table_1 AS a
                 WHERE table_1.n = a.n);
  • Correlated subqueries cannot contain SELECT AND CONSUME statements.
  • Correlated subqueries cannot contain the TOP n operator.
  • You can specify a SELECT in an outer query, a main query, or a subquery.
    SELECT Specified Description
    Main query Any table referenced in the main query must be specified in the FROM clause of the main query SELECT.
    Subquery Any table referenced in the subquery must be specified in its FROM clause of that query or in an outer query.

    If the select expression list of the subquery specifies a column reference to a table in the FROM clause of an outer query, the column reference must be fully qualified.

    If the correlated condition specifies an unqualified column reference, then Vantage searches for the column starting with the tables and views in the current subquery toward the tables and views in the outermost query.
  • Correlated subqueries can be specified in a FROM clause under the following conditions:
    • If a table referenced in a subquery is identified in the FROM clause of a subquery, the table reference is local and the subquery is noncorrelated.
    • If a table referenced in a subquery is identified in the FROM clause of an outer query, the table reference is not local and the subquery is correlated.
The following rules apply to specifying a FROM clause in a correlated subquery.
  • A FROM clause is required in any subquery specification.
  • The FROM clause is optional in the outer query. However, write your applications to specify a FROM clause because future releases may enforce the ANSI SQL:2011 rule that tables referenced in a SELECT must be specified in a FROM clause. Specifying tables explicitly in the FROM clause enhances their clarity and helps prevent coding errors.

    Vantage returns a warning message if you do not specify a referenced table in the FROM clause.