Contents Index Quantified comparison tests with ANY and ALL The ALL test

ASA SQL User's Guide
  Using Subqueries
    Quantified comparison tests with ANY and ALL

The ANY test


The ANY test, used in conjunction with one of the SQL comparison operators (=, <>, <, <=, >, >=), compares 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 order and customer IDs of those orders placed after the first product of the order #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 product of the order #2005. If an order date is greater than the shipping date for one shipment of order #2005, then that id and customer id from the sales_order table are part of the result set. The ANY test is thus analogous to the OR operator: the above query can be read, "Was this sales order placed after the first product of the order #2005 was shipped, or after the second product of order #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 placed after any products of order #2005 were shipped." But this means the query will return the order IDs and customer IDs for the orders placed after all products of order #2005 were shipped—which is not what the query does.

Instead, try reading the query like this: "Return the order and customer IDs for those orders placed after at least one product of order #2005 was 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:


Contents Index Quantified comparison tests with ANY and ALL The ALL test