Subquery that follows IN

The optimizer converts a subquery that follows an IN keyword only if:

  • The main query does not contain a GROUP BY clause, and is not an aggregate query, or the subquery returns exactly one value.

  • The subquery does not contain a GROUP BY clause.

  • The subquery does not contain the keyword DISTINCT.

  • The subquery is not a UNION query.

  • The subquery is not an aggregate query.

  • The conjunct 'expression IN ( subquery-expression )' must not be negated.

 Example
 A UNION query following the IN operator cannot be converted
 Query with IN operator converted to a query with an ANY operator