Quantified Comparisons
When a comparison operator is used together with the words ALL, ANY, or SOME, the comparison is known as being “quantified.” Such comparisons operate on subqueries that could return multiple rows but would return a single column.
Syntax
<expression> <comparison-operator> {ALL |ANY |SOME} <column-subquery>
Remarks
• <comparison-operator> can be <, =, >, <=, >=, <>.
• ALL or ANY is applicable only to subqueries. When one of them is used, the comparison converts a scalar subquery to a column subquery.
• Except for use in subqueries, ANY and SOME are equivalent.
• If ALL is used, the comparison must be true for all values returned by the subquery.
• If ANY or SOME is used, the comparison must be true for at least one value of the subquery.
• A subquery using ANY must return a single column. ANY compares a single value to the column of data values produced by the subquery.
If any of the comparisons yields a value of TRUE, the ANY comparison returns TRUE. If the subquery returns NULL, the ANY comparison returns FALSE.
• ALL is used to compare a single value to the data values produced by the subquery. The specified comparison operator is used to compare the given value to each data value in the result set. If all of the comparisons returns a value of TRUE, the ALL test also returns TRUE.
• If the subquery returns an empty result set, the ALL test returns a value of TRUE.
If the comparison test is false for any values in the result set, the ALL search returns FALSE.
The ALL search returns TRUE if all the values are true. Otherwise, it returns UNKNOWN. For example, if there is a NULL value in the subquery result set but the search condition is TRUE for all non-null values, the ALL test returns UNKNOWN.
• Negating an ALL comparison is not equivalent to using an ALL comparison with any other combination of operators. For example, NOT a = ALL (subquery) is not equivalent to a <> ALL (subquery).
Example (Using ANY)
This query returns the order ID and customer ID for orders placed after at least one product with an order ID of 500 was shipped.
SELECT ID, CustomerID
FROM SalesOrders
WHERE OrderDate > ANY (
SELECT ShipDate
FROM SalesOrderItems
WHERE ID=500);
Example (Using SOME)
You can use SOME instead of ANY, as in the following example:
SELECT ID, CustomerID
FROM SalesOrders
WHERE OrderDate > SOME (
SELECT ShipDate
FROM SalesOrderItems
WHERE ID=500);
Example (Using ALL)
The main query tests the order dates for each order against the shipping dates of every product with the ID 500. If an order date is greater than the shipping date for every shipment with order ID 500, the ID and customer ID from the SalesOrders table are included in the result set.
SELECT ID, CustomerID
FROM SalesOrders
WHERE OrderDate > ALL (
SELECT ShipDate
FROM SalesOrderItems
WHERE ID=500);