Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 12.0.1 » SQL Anywhere Server - SQL Usage » Query and modify data » Use of subqueries » Optimizer automatic conversion of subqueries to joins

 

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