Correlated and non-correlated subqueries

Different subqueries require different approaches for efficient processing by Db2.

All subqueries can be classified into either two categories: correlated and non-correlated

Correlated subqueries

Correlated subqueries contain a reference to a table or column that is outside of the scope of the subquery.

In the following query, for example, the correlation name X is a value from a table that is not listed in the FROM clause of the subquery. The inclusion of X illustrates that the subquery references the outer query block:

SELECT * FROM DSN8C10.EMP X
   WHERE  JOB = 'DESIGNER'
      AND  EXISTS (SELECT 1
                  FROM   DSN8C10.PROJ
                  WHERE  DEPTNO = X.WORKDEPT
                     AND  MAJPROJ = 'MA2100');

Non-correlated subqueries

Non-correlated subqueries do not refer to any tables or columns that are outside of the scope of the subquery.

The following example query refers only to tables are within the scope of the FROM clause.

SELECT * FROM DSN8C10.EMP
  WHERE  JOB = 'DESIGNER'
    AND  WORKDEPT IN (SELECT DEPTNO
                      FROM   DSN8C10.PROJ
                      WHERE  MAJPROJ = 'MA2100');