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

SQL Anywhere 12.0.0 » SQL Anywhere Server - SQL Usage » Querying and modifying data » Using subqueries » Optimizer automatic conversion of subqueries to joins

 

Subquery that follows ANY, ALL or SOME

A subquery that follows the keywords ALL, ANY, or SOME is called a quantified comparison. The optimizer converts these subqueries to joins 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 comparison-operator { ANY | SOME } ( subquery-expression )' must not be negated.

  • The conjunct 'expression comparison-operator ALL ( subquery-expression )' must be negated.

The first four of these conditions are relatively straightforward.

 Example
 Negating subqueries with the ANY and ALL operators
 Negating the ANY and ALL expressions