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:

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 : IN or ANY Subquery Transformations for Subqueries Returning a Single Row Before Transformation After 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 ...)