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.
IN or ANY subquery transformations for subqueries that return a single
row
This table shows how IN and ANY subqueries are transformed.
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 ...)