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 the following table.

Table 1. IN or ANY subquery transformations for subqueries that return 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 ...)
Related concepts
Materialization
Flattening a subquery into a normal join
Flattening a subquery into an EXISTS join
Flattening VALUES subqueries
DISTINCT elimination in IN, ANY, and EXISTS subqueries