User's Guide
   PART 1. Working with Databases
     CHAPTER 7. Using Subqueries       

Quantified comparison tests with ANY and ALL

The quantified comparison test is broken into two categories, the ALL test and the ANY test:

  The ANY test

The ANY test is used in conjunction with one of the SQL comparison operators (=, <>, <, <=, >, >=) to compare a single value to the column of data values produced by the subquery. To perform the test, SQL uses the specified comparison operator to compare the test value to each data value in the column. If any of the comparisons yields a TRUE result, the ANY test returns TRUE.

A subquery used with ANY must return a single column.

Example 

Find the customer and product ID's of those products that were ordered after the first order of product #2005 was shipped.

SELECT id, cust_id 
FROM sales_order 
WHERE order_date > ANY (
   SELECT ship_date 
   FROM sales_order_items 
   WHERE id=2005)

id	cust_id
2006	105
2007	106
2008	107
2009	108
...	...

In executing this query, the main query tests the order dates for each order against the shipping dates of every order of product #2005. If an order date is greater than the shipping date for one shipment of product #2005, then the id and customer id from the sales_order table are displayed. The ANY test is thus analogous to the OR operator: the above query can be read, "Was this sales order placed after the first order of product #2005 was shipped, or after the second order of product #2005 was shipped, or..."

Understanding the ANY operator 

The ANY operator can be a bit confusing. It is tempting to read the query as "Return those orders which were placed after any orders of product #2005 were shipped". But this means that the query will return the order ID's and customer ID's for the orders placed after all orders of product #2005 were shipped - which is not what the query does!

Instead, try reading the query like this: "Return the order ID's and customer ID's for those orders which were placed after at least one order of product #2005 were shipped." Using the keyword SOME may provide a more intuitive way to phrase the query; the following query is equivalent to the previous query.

SELECT id, cust_id 
FROM sales_order 
WHERE order_date > SOME (
   SELECT ship_date 
   FROM sales_order_items 
   WHERE id=2005)

The keyword SOME is equivalent to the keyword ANY.

Notes about the ANY operator 

There are two additional important characteristics of the ANY test:

• Empty subquery results     If the subquery produces an empty column of query results, the ANY test returns FALSE. This makes sense, as if there are no results, then it is not true that at least one result satisfies the comparison test.

• NULL values in column     If the comparison test is not FALSE for any data value in the column, and is NULL for one or more values, the ANY search returns NULL. This is because in this situation, you cannot conclusively state whether there is a value for the subquery for which the comparison test holds; there may or may not be, depending on the "correct" values for the NULL data.

  The ALL test

Like the ANY test, the ALL test is used in conjunction with one of the six SQL comparison operators (=, <>, <, <=, >, >=) to compare a single value to the column of data values produced by the subquery. To perform the test, SQL uses the specified comparison operator to compare the test value to each data value in the column. If all of the comparisons yield TRUE results, the ALL test returns TRUE.

Example 

Here is a request that is naturally handled with the ALL test: "Find the customer and product ID's of those products that were ordered after all orders of product #2001 were shipped."

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

id	cust_id
2002	102
2003	103
2004	104
2005	101
...	...

In executing this query, the main query tests the order dates for each order against the shipping dates of every order of product #2001. If an order date is greater than the shipping date for every shipment of product #2001, then the id and customer id from the sales_order table are returned. The ALL test is thus analogous to the AND operator: the above query can be read, "Was this sales order placed before the first order of product #2001 was shipped, and before the second order of product #2001 was shipped, and..."

Notes about the ALL operator 

There are three additional important characteristics of the ALL test:

• Empty subquery results     If the subquery produces an empty column of query results, the ALL test returns TRUE. This makes sense, as if there are no results, then it is true that the comparison test holds for every value in the result set.

• NULL values in column     If the comparison test is not FALSE for any data value in the column, and is NULL for one or more values, the ALL search returns NULL. This is because in this situation, you cannot conclusively state whether the comparison test holds for every value in the subquery result set; it may or may not, depending on the "correct" values for the NULL data.

• Negating the ALL test     The expressions
  NOT a = ALL (subquery)
  and
  a <> ALL (subquery)
  are not equivalent. This will be explained in detail in How subqueries are executed: Quantified comparison test.