When DB2 transforms a subquery into a join

DB2® might sometimes transform a subquery into a join for SELECT, UPDATE, and DELETE statements.

Begin program-specific programming interface information.

When SELECT, UPDATE and DELETE statements contain subqueries, DB2 might transform the statements to use joins instead of the subqueries. The order of access for a subquery is more restrictive than for an equivalent join. Therefore, DB2 might gain more flexibility for optimizing an access path by using the join.

However, DB2 cannot always transform every subquery to a join. For example, DB2 does not transform a query when the transformation would introduce redundancy, or when the subquery contains certain clauses or predicate types.

However, the specific criteria for transformation are not described here. The purpose of the transformation is to provide the additional flexibility for optimization without a rewrite. Consequently, the recommendation for any rewrite is to use the join explicitly rather than making the subquery eligible for transformation.

The following subquery can be transformed into a join because it meets the above conditions for transforming a SELECT statement:

SELECT * FROM EMP
  WHERE DEPTNO IN 
   (SELECT DEPTNO FROM DEPT
      WHERE LOCATION IN ('SAN JOSE', 'SAN FRANCISCO')
      AND DIVISION = 'MARKETING');

If a department in the marketing division has branches in both San Jose and San Francisco, the result of the SQL statement is not the same as if a join were performed. The join makes each employee in this department appear twice because it matches once for the department of location San Jose and again of location San Francisco, although it is the same department. Therefore, it is clear that to transform a subquery into a join, the uniqueness of the subquery select list must be guaranteed. For this example, a unique index on any of the following sets of columns would guarantee uniqueness:

(DEPTNO)
(DIVISION, DEPTNO)
(DEPTNO, DIVISION).

The resulting query after the transformation has the following form:

SELECT EMP.* FROM EMP, DEPT
   WHERE EMP.DEPTNO = DEPT.DEPTNO AND
         DEPT.LOCATION IN ('SAN JOSE', 'SAN FRANCISCO') AND
         DEPT.DIVISION = 'MARKETING';

End program-specific programming interface information.