Correlation names in references

A correlation name is a name that you specify for a table, view, nested table expression or table function. This name is valid only within the context in which it is defined. Use correlation names to avoid ambiguity, to establish correlated references, or to use shorter names for tables or views.

A correlated reference can appear in a subquery, in a nested table expression, or as an argument of a user-defined table function. For information about correlated references in nested table expressions and table functions, see Joining data from more than one table. In a subquery, the reference should be of the form X.C, where X is a correlation name and C is the name of a column in the table that X represents.

Any number of correlated references can appear in a subquery, with no restrictions on variety. For example, you can use one correlated reference in the outer SELECT, and another in a nested subquery.

When you use a correlated reference in a subquery, the correlation name can be defined in the outer SELECT or in any of the subqueries that contain the reference. Suppose, for example, that a query contains subqueries A, B, and C, and that A contains B and B contains C. The subquery C can use a correlation reference that is defined in B, A, or the outer SELECT.

You can define a correlation name for each table name in a FROM clause. Specify the correlation name after its table name. Leave one or more blanks between a table name and its correlation name. You can include the word AS between the table name and the correlation name to increase the readability of the SQL statement.

The following example demonstrates the use of a correlated reference in the search condition of a subquery:

SELECT EMPNO, LASTNAME, WORKDEPT, EDLEVEL
  FROM DSN8A10.EMP AS X
  WHERE EDLEVEL >
    (SELECT AVG(EDLEVEL)
       FROM DSN8A10.EMP
       WHERE WORKDEPT = X.WORKDEPT);

The following example demonstrates the use of a correlated reference in the select list of a subquery:

UPDATE BP1TBL T1
  SET (KEY1, CHAR1, VCHAR1) = 
    (SELECT VALUE(T2.KEY1,T1.KEY1), VALUE(T2.CHAR1,T1.CHAR1),
            VALUE(T2.VCHAR1,T1.VCHAR1) 
       FROM BP2TBL T2
       WHERE (T2.KEY1 = T1.KEY1))
  WHERE KEY1 IN 
    (SELECT KEY1
       FROM BP2TBL T3
       WHERE KEY2 > 0);

Using correlated subqueries in an UPDATE statement:

Use correlation names in an UPDATE statement to refer to the rows that you are updating. The subquery for which you specified a correlation name is called a correlated subquery.

For example, when all activities of a project must complete before September 2006, your department considers that project to be a priority project. Assume that you have added the PRIORITY column to DSN8A10.PROJ. You can use the following SQL statement to evaluate the projects in the DSN8A10.PROJ table, and write a 1 (a flag to indicate PRIORITY) in the PRIORITY column for each priority project:

UPDATE DSN8A10.PROJ X
SET PRIORITY = 1
WHERE DATE('2006-09-01') >
  (SELECT MAX(ACENDATE)
     FROM DSN8A10.PROJACT
     WHERE PROJNO = X.PROJNO);

As DB2® examines each row in the DSN8A10.PROJ table, it determines the maximum activity end date (the ACENDATE column) for all activities of the project (from the DSN8A10.PROJACT table). If the end date of each activity that is associated with the project is before September 2006, the current row in the DSN8A10.PROJ table qualifies, and DB2 updates it.

Using correlated subqueries in a DELETE statement:

Use correlation names in a DELETE statement to refer to the rows that you are deleting. The subquery for which you specified a correlation name is called a correlated subquery. DB2 evaluates the correlated subquery once for each row in the table that is named in the DELETE statement to decide whether to delete the row.

Using tables with no referential constraints:

Suppose that a department considers a project to be complete when the combined amount of time currently spent on it is less than or equal to half of a person's time. The department then deletes the rows for that project from the DSN8A10.PROJ table. In the examples in this topic, PROJ and PROJACT are independent tables; that is, they are separate tables with no referential constraints defined on them.

DELETE FROM DSN8A10.PROJ X
  WHERE .5 >
    (SELECT SUM(ACSTAFF)
       FROM DSN8A10.PROJACT
       WHERE PROJNO = X.PROJNO);

To process this statement, DB2 determines for each project (represented by a row in the DSN8A10.PROJ table) whether the combined staffing for that project is less than 0.5. If it is, DB2 deletes that row from the DSN8A10.PROJ table.

To continue this example, suppose that DB2 deletes a row in the DSN8A10.PROJ table. You must also delete rows that are related to the deleted project in the DSN8A10.PROJACT table. To do this, use a statement similar to this statement:

DELETE FROM DSN8A10.PROJACT X
  WHERE NOT EXISTS
    (SELECT *
       FROM DSN8A10.PROJ
       WHERE PROJNO = X.PROJNO);

DB2 determines, for each row in the DSN8A10.PROJACT table, whether a row with the same project number exists in the DSN8A10.PROJ table. If not, DB2 deletes the row from DSN8A10.PROJACT.

Using a single table:

A subquery of a searched DELETE statement (a DELETE statement that does not use a cursor) can reference the same table from which rows are deleted. In the following statement, which deletes the employee with the highest salary from each department, the employee table appears in the outer DELETE and in the subselect:

DELETE FROM YEMP X
  WHERE SALARY = (SELECT MAX(SALARY) FROM YEMP Y
                  WHERE X.WORKDEPT =Y.WORKDEPT);

This example uses a copy of the employee table for the subquery.

The following statement, without a correlated subquery, yields equivalent results:

DELETE FROM YEMP
  WHERE (SALARY, WORKDEPT) IN (SELECT MAX(SALARY), WORKDEPT
                               FROM YEMP
                               GROUP BY WORKDEPT);

Using tables with referential constraints:

DB2 restricts delete operations for dependent tables that are involved in referential constraints. If a DELETE statement has a subquery that references a table that is involved in the deletion, make the last delete rule in the path to that table RESTRICT or NO ACTION. This action ensures that the result of the subquery is not materialized before the deletion occurs. However, if the result of the subquery is materialized before the deletion, the delete rule can also be CASCADE or SET NULL.

Example: Without referential constraints, the following statement deletes departments from the department table whose managers are not listed correctly in the employee table:

DELETE FROM DSN8A10.DEPT THIS
  WHERE NOT DEPTNO =
    (SELECT WORKDEPT
       FROM DSN8A10.EMP
       WHERE EMPNO = THIS.MGRNO);

With the referential constraints that are defined for the sample tables, this statement causes an error because the result table for the subquery is not materialized before the deletion occurs. Because DSN8A10.EMP is a dependent table of DSN8A10.DEPT, the deletion involves the table that is referred to in the subquery, and the last delete rule in the path to EMP is SET NULL, not RESTRICT or NO ACTION. If the statement could execute, its results would depend on the order in which DB2 accesses the rows. Therefore, DB2 prohibits the deletion.