ASA SQL User's Guide
  Using Subqueries
    How subqueries work

Quantified comparison test

A subquery that follows one of the keywords ALL, ANY and SOME is converted into a join only if it satisfies certain criteria.

• 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)' must be negated.

• The conjunct 'expression comparison-operator ALL (subquery)' must not 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 order_date, sales_rep
FROM sales_order
WHERE cust_id = ANY (
   SELECT id
   FROM customer
   WHERE lname = 'Clarke' OR fname = 'Suresh')

Alternately, it can be phrased in join form

SELECT fname, lname, order_date, sales_rep
FROM sales_order, customer
WHERE cust_id=customer.id AND (lname = 'Clarke' OR fname = '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 thus cannot be converted to a join:

SELECT order_date, sales_rep
FROM sales_order
WHERE cust_id = ANY (
   SELECT id
   FROM customer
   WHERE lname = 'Clarke' OR fname = 'Suresh'
   UNION
   SELECT emp_id
   FROM employee)

Similarly, the request "Find the order IDs and customer IDs of those orders that were not placed after all products of order #2001 were shipped," is naturally expressed with a subquery:

SELECT id, cust_id
FROM sales_order
WHERE NOT order_date > ALL (
   SELECT ship_date
   FROM sales_order_items
   WHERE id=2001)

It would be converted to the join:

SELECT sales_order.id, cust_id
FROM sales_order, sales_order_items
WHERE (sales_order_items.id=2001) and (order_date <= ship_date)

However, 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:

SELECT id, cust_id
FROM sales_order
WHERE NOT order_date > ALL (
   SELECT first (ship_date)
   FROM sales_order_items )

Therefore, it would not be converted to a join.

Negating subqueries with the ANY and ALL operators 

The fifth criterion is a little more puzzling: queries of the form

SELECT select-list 
FROM table 
WHERE NOT expression comparison-operator ALL( subquery ) 
are converted to joins, as are queries of the form

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

but the queries

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

and

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

are not.

Logical equivalence of ANY and ALL expressions 

This is because the first two queries are in fact 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. Just as the expression

NOT ((X > A) AND (X > B))

is equivalent to the expression

(X <= A) OR (X <= B)

the expression

NOT order_date > ALL (
   SELECT first (ship_date)
   FROM sales_order_items )

is equivalent to the expression

order_date <= ANY (
   SELECT first (ship_date)
   FROM sales_order_items )

Negating the ANY and ALL expressions 

In general, the expression

NOT column-name operator ANY( subquery )

is equivalent to the expression

column-name inverse-operator ALL( subquery )

and the expression

NOT column-name operator ALL( subquery )

is equivalent to the expression

column-name inverse-operator ANY( subquery )

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

Table of operators and their inverses 

The following table lists the inverse of each operator.