Column name qualifiers in correlated references

A reference to a column of a table identified at a higher level is called a correlated reference. Because the same table or view can be identified at many levels, unique correlation names are recommended as table designators. It is good practice to use these unique correlation names to qualify column names.

A subselect is a form of a query that can be used as a component of various SQL statements. A subquery is a form of a fullselect that is enclosed within parenthesis. For example, a subquery can be used in a search condition. A fullselect that is used to retrieve a single value as an expression within a statement is called a scalar fullselect or a scalar subquery. A fullselect that is 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. Thus, 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 a MERGE or UPDATE, where it is the table or view being updated. A search condition of a subquery 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 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.

A qualified column name, Q.C, is a correlated reference only if these three conditions are met:

  • Q.C is used in a search condition or in a select list of a subquery.
  • Q does not name a table used in the FROM clause of that subquery.
  • Q does name a table used at some higher level.

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 name a table at more than one level, Q.C refers to the lowest level that contains the subquery that includes Q.C.

If a correlation name is defined as the table designator of the table or view, but the table or view name is used as the column qualifier instead of the correlation name, an error is returned.

For example, in the following statement, the correlated reference X.WORKDEPT (in the last line) refers to the value of WORKDEPT in table DSN8D10.EMP at the level of the first FROM clause (which establishes X as a correlation name for DSN8D10.EMP.). The statement lists employees who make less than the average salary for their department. 
   SELECT EMPNO, LASTNAME, WORKDEPT
     FROM DSN8D10.EMP X
     WHERE SALARY < (SELECT AVG(SALARY)
                       FROM DSN8D10.EMP
                       WHERE WORKDEPT = X.WORKDEPT);
The following example shows a correlated reference in the select list of the subquery. 
   SELECT T1.KEY1
     FROM BP1TBL T1
     GROUP BY T1.KEY1
     HAVING MAX(T1.KEY1) = (SELECT MIN(T1.KEY1) + MIN(T2.KEY1)
                       FROM BP2TBL T2);