Using 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-10-04
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
Teradata Vantage™
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, one of them must be renamed with a correlation name.

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

    Also note that 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 should be specified in the FROM clause of the main query SELECT.
    Subquery Any table referenced in that subquery must be specified either in the FROM clause of that query or in some 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 … THEN the table reference is …
    a subquery local and the subquery is noncorrelated.
    an outer query 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 not required in the outer query. However, you should always write your applications to specify a FROM clause because future releases might enforce the ANSI SQL:2011 rule that all tables referenced in a SELECT must be specified in a FROM clause. More importantly, specifying tables explicitly in the FROM clause helps to prevent errors in coding your queries because it enhances their clarity.

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