# 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 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 ...)`

Parent topic: Subquery processing and transformations
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