A subselect is a form of a query that can be used as a component of various SQL statements. Refer to Queries for more information about subselects. A subquery is a form of a fullselect that is enclosed within parentheses. For example, a subquery can be used in a search condition. A fullselect used in the FROM clause of a query is called a nested table expression.
A subquery can include search conditions of its own, and these search conditions can, in turn, include subqueries. Therefore, an SQL statement can contain a hierarchy of subqueries. Those elements of the hierarchy that contain subqueries are said to be at a higher level than the subqueries they contain.
Every element of the hierarchy has a clause that establishes one or more table designators. This is the FROM clause, except in the highest level of an UPDATE or DELETE statement. A search condition, the select list, the join clause, an argument of a table function in a subquery, or a nested table expression that is preceded by the LATERAL keyword can reference not only columns of the tables identified by the FROM clause of its own element of the hierarchy, but also columns of tables identified at any level along the path from its own element to the highest level of the hierarchy. A reference to a column of a table identified at a higher level is called a correlated reference. A reference to a column of a table identified at the same level from a nested table expression through the use of the LATERAL keyword is called lateral correlation.
A correlated reference to column C of table T can be of the form C, T.C, or Q.C, if Q is a correlation name defined for T. However, a correlated reference in the form of an unqualified column name is not good practice. The following explanation is based on the assumption that a correlated reference is always in the form of a qualified column name and that the qualifier is a correlation name.
Q.C is a correlated reference only if these three conditions are met:
Q.C refers to column C of the table or view at the level where Q is used as the table designator of that table or view. Because the same table or view can be identified at many levels, unique correlation names are recommended as table designators. If Q is used to designate a table at more than one level, Q.C refers to the lowest level that contains the subquery that includes Q.C.
In the following statement, Q is used as a correlation name for T1 and T2, but Q.C refers to the correlation name associated with T2, because it is the lowest level that contains the subquery that includes Q.C.
SELECT * FROM T1 Q WHERE A < ALL (SELECT B FROM T2 Q WHERE B < ANY (SELECT D FROM T3 WHERE D = Q.C))
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.