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

The request "When did Ms. Clarke and Suresh place their orders, and by which sales representatives?" can be handled in subquery form:

SELECT OrderDate, SalesRepresentative
FROM SalesOrders
WHERE CustomerID = ANY (
   SELECT ID
   FROM Customers
   WHERE Surname = 'Clarke' OR GivenName = 'Suresh' );

Alternately, it can be phrased in join form

SELECT OrderDate, SalesRepresentative
FROM SalesOrders, Customers
WHERE CustomerID=Customers.ID AND 
  ( Surname = 'Clarke' OR GivenName = 'Suresh' );

However, the request, "When did Ms. Clarke, Suresh, and any employee who is also a customer, place their orders?" would be phrased as a union query, and cannot be converted to a join:

SELECT OrderDate, SalesRepresentative
FROM SalesOrders
WHERE CustomerID = ANY (
   SELECT ID
   FROM Customers
   WHERE Surname = 'Clarke' OR GivenName = 'Suresh'
   UNION
   SELECT EmployeeID
   FROM Employees );

Similarly, the request "Find the order IDs and customer IDs of those orders not shipped after the first shipping dates of all the products" would be phrased as the aggregate query, and therefore cannot be converted to a join:

SELECT ID, CustomerID
FROM SalesOrders
WHERE NOT OrderDate > ALL (
   SELECT FIRST ( ShipDate )
   FROM SalesOrderItems 
   ORDER BY ShipDate );

Negating subqueries with the ANY and ALL operators

The fifth criterion is a little more puzzling. Queries taking the following form are converted to joins:

SELECT select-list 
FROM table 
WHERE NOT expression comparison-operator ALL ( subquery-expression )

SELECT select-list
FROM table
WHERE expression comparison-operator ANY ( subquery-expression )

However, the following queries are not converted to joins:

SELECT select-list
FROM table
WHERE expression comparison-operator ALL ( subquery-expression )

SELECT select-list
FROM table
WHERE NOT expression comparison-operator ANY ( subquery-expression )

The first two queries are equivalent, as are the last two. Recall that the ANY operator is analogous to the OR operator, but with a variable number of arguments; and that the ALL operator is similarly analogous to the AND operator. For example, the following two expressions are equivalent:

NOT ( ( X > A ) AND ( X > B ) )
( X <= A ) OR ( X <= B )

The following two expressions are also equivalent:

WHERE NOT OrderDate > ALL (
   SELECT FIRST ( ShipDate )
   FROM SalesOrderItems
   ORDER BY ShipDate )

WHERE OrderDate <= ANY (
   SELECT FIRST ( ShipDate )
   FROM SalesOrderItems
   ORDER BY ShipDate )

Negating the ANY and ALL expressions

In general, the following expressions are equivalent:

NOT column-name operator ANY ( subquery-expression )

column-name inverse-operator ALL ( subquery-expression )

These expressions are generally equivalent as well:

NOT column-name operator ALL ( subquery-expression )

column-name inverse-operator ANY ( subquery-expression )

where inverse-operator is obtained by negating operator, as shown in the table below:

operator	inverse-operator
=	<>
<	=>
>	=<
=<	>
=>	<
<>	=

Discuss this page in DocCommentXchange.