|
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.
|
