apache > db
Apache DB Project
 
Font size:      

IN/ANY Subquery Transformation

IN/ANY Subquery Transformation

An IN or ANY subquery that is guaranteed to return at most one row can be transformed into an equivalent expression subquery (a scalar subquery without the IN or ANY). The subquery must not be correlated. Subqueries guaranteed to return at most one row are:

  • Simple VALUES clauses
  • SELECTs returning a non-grouped aggregate

For example:

WHERE C1 IN (SELECT MIN(c1) FROM T)

can be transformed into

WHERE C1 = (SELECT MIN(c1) FROM T)

This transformation is considered before subquery materialization. If the transformation is performed, the subquery becomes materializable. In the example, if the IN subquery were not transformed, it would be evaluated anew for each row.

The subquery type transformation is shown in Table 5:

Table 5. IN or ANY Subquery Transformations for Subqueries Returning a Single Row

Before TransformationAfter Transformation
c1 IN (SELECT ...) c1 = (SELECT ...)
c1 = ANY (SELECT ...) c1 = (SELECT ...)
c1 <> ANY (SELECT ...) c1 <> (SELECT ...)
c1 > ANY (SELECT ...) c1 > (SELECT ...)
c1 >= ANY (SELECT ...) c1 >= (SELECT ...)
c1 < ANY (SELECT ...) c1 < (SELECT ...)
c1 <= ANY (SELECT ...) c1 <= (SELECT ...)

Previous Page
Next Page
Table of Contents
Index