The ALL test is used with one of the 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 the comparisons yield TRUE results, the ALL test returns TRUE.
This example finds the order and customer IDs of orders placed after all products of order #2001 were shipped.
SELECT ID, CustomerID FROM SalesOrders WHERE OrderDate > ALL ( SELECT ShipDate FROM SalesOrderItems WHERE ID=2001 ); |
ID | CustomerID |
---|---|
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 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 SalesOrders table are part of the result set. The ALL test is 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..."
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 Subquery that follows ANY, ALL or SOME.
Discuss this page in DocCommentXchange. Send feedback about this page using email. |
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 |