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

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 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 result set. If all of the comparisons yield TRUE results, the ALL test returns TRUE.

Example 

Here is a request naturally handled with the ALL test: "Find the order and customer IDs of those orders placed after all products of order #2001 were shipped."

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

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

Notes about the ALL operator 

There are three additional important characteristics of the ALL test:

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

• NULL values in subquery result set    If the comparison test is false for any values in the result set, the ALL search returns FALSE. It returns TRUE if all values are true. Otherwise, it returns UNKNOWN—for example, this can occur if there is a NULL value in the subquery result set but the search condition is TRUE for all non-NULL values.

• Negating the ALL test    The following expressions are not equivalent.

  NOT a = ALL (subquery)
  a <> ALL (subquery)

  For more information about this test, see Quantified comparison test.