Subqueries and Query Expressions - The ANY Test *
(Page 5 of 12 )
The ANY test is used in conjunction with one of the six SQL comparison operators (=, <>, <, <=, >, >=) to compare a single test value to a column of data values produced by a subquery. To perform the test, SQL uses the specified comparison operator to compare the test value to each data value in the column, one at a time. If any of the individual comparisons yield a TRUE result, the ANY test returns a TRUE result.
Here is an example of a request that can be handled with the ANY test:
List the salespeople who have taken an order that represents more than 10 percent of their quota.
SELECT NAME
FROM SALESREPS
WHERE (.1 * QUOTA) < ANY (SELECT AMOUNT
FROM ORDERS
WHERE REP = EMPL_NUM)
NAME
--------------
Sam Clark
Larry Fitch
Nancy Angelli
Conceptually, the main query tests each row of the
SALESREPS table, one by one. The subquery finds all of the orders taken by the current salesperson and returns a column containing the order amounts for those orders. The WHERE clause of the main query then computes 10 percent of the current salesperson’s quota and uses it as a test value, comparing it to every order amount produced by the subquery. If any order amount exceeds the calculated test value, the ANY test returns TRUE, and the salesperson is included in the query results. If not, the salesperson is not included inthe query results. The keyword SOME is an alternative for ANY specified by the ANSI/ISO SQL standard. Either keyword can generally be used, but some DBMS brands do not support SOME.The
ANY test can sometimes be difficult to understand because it involves an entire set of comparisons, not just one. It helps if you read the test in a slightly different way than it appears in the statement. If this ANY test appears: WHERE X < ANY (SELECT Y …)
instead of reading the test like this:
"where X is less than any select Y…"
try reading it like this:"where, for some Y, X is less than Y"
When you use this trick, the preceding query becomes:
Select the salespeople where, for some order taken by the salesperson, 10 percent of the salesperson’s quota is less than the order amount.
If the subquery in an ANY test produces no rows of query results, or if the query results include NULL values, the operation of the ANY test may vary from one DBMS to another. The ANSI/ISO SQL standard specifies these detailed rules describing the results of the ANY test when the test value is compared to the column of subquery results:
- If the subquery produces an empty column of query results, the ANY test returns FALSE—there is no value produced by the subquery for which the comparison test holds.
- If the comparison test is TRUE for at least one of the data values in the column, then the ANY search condition returns TRUE—there is indeed some value produced by the subquery for which the comparison test holds.
- If the comparison test is FALSE for every data value in the column, then the ANY search condition returns FALSE. In this case, you can conclusively state that there is no value produced by the subquery for which the comparison test holds.
- If the comparison test is not TRUE for any data value in the column, but it is NULL (unknown) for one or more of the data values, then the ANY search condition returns NULL. In this situation, you cannot conclusively state whether there is a value produced by the subquery for which the comparison test holds; there may or may not be, depending on the “actual” (but currently unknown) values for the NULL data.
The ANY comparison operator can be very tricky to use in practice, especially in conjunction with the inequality (<>) comparison operator. Here is an example that shows the problem:
List the names and ages of all the people in the sales force who do not manage an office.
It’s tempting to express this query as shown in this example:
SELECT NAME, AGE
FROM SALESREPS
WHERE EMPL_NUM <> ANY (SELECT MGR
FROM OFFICES)
The subquery:
SELECT MGR
FROM OFFICES
obviously produces the employee numbers of the managers, and therefore the query seems to be saying:
Find each salesperson who is not the manager of any office.
But that’s not what the query says! What it does say is this:
Find each salesperson who, for some office, is not the manager of that office.
Of course for any given salesperson, it’s possible to find some office where that salesperson is not the manager. The query results would include all the salespeople and therefore fail to answer the question that was posed! The correct query is:
SELECT NAME, AGE
FROM SALESREPS
WHERE NOT (EMPL_NUM = ANY (SELECT MGR
FROM OFFICES
NAME AGE AGE
--------------- -----
Mary Jones 31
Sue Smith 48
Dan Roberts 45
Tom Snyder 41
Paul Cruz 29
Nancy Angelli 49
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 test value to a column of data values produced by a subquery. To perform the test, SQL uses the specified comparison operator to compare the test value to each data value in the column, one at a time. If all of the individual comparisons yield a TRUE result, the ALL test returns a TRUE result
Here is an example of a request that can be handled with the ALL test:
List the offices and their targets where all of the salespeople have sales that exceed 50 percent of the office’s target.
SELECT CITY, TARGET
FROM OFFICES
WHERE (.50 * TARGET) < ALL (SELECT SALES
FROM SALESREPS
WHERE REP_OFFICE = OFFICE)
CITY TARGET
----------- --------------
| Denver | $300,000.00 |
| New York | $575,000.00 |
| Atlanta | $350,000.00 |
Conceptually, the main query tests each row of the OFFICES table, one by one. The subquery finds all of the salespeople who work in the current office and returns a column containing the sales for each salesperson. The WHERE clause of the main query then computes 50 percent of the office’s target and uses it as a test value, comparing it to every sales value produced by the subquery. If all of the sales values exceed the calculated test value, the ALL test returns TRUE, and the office is included in the query results. If not, the office is not included in the query results.
Like the ANY test, the ALL test can be difficult to understand because it involves an entire set of comparisons, not just one. Again, it helps if you read the test in a slightly different way than it appears in the statement. If this ALL test appears:
WHERE X < ALL (SELECT Y …)
instead of reading it like this:
"where X is less than all select Y…"
try reading the test like this:
"where, for all Y, X is less than Y"
When you use this trick, the preceding query becomes:
Select the offices where, for all salespeople who work in the office, 50 percent of the office’s target is less than the salesperson’s sales.
If the subquery in an ALL test produces no rows of query results, or if the query results include NULL values, the operation of the ALL test may vary from one DBMS to another. The ANSI/ISO SQL standard specifies these detailed rules describing the results of the ALL test when the test value is compared to the column of subquery results:
- If the subquery produces an empty column of query results, the ALL test returns TRUE. The comparison test does hold for every value produced by the subquery; there just aren’t any values.
- If the comparison test is TRUE for every data value in the column, then the ALL search condition returns TRUE. Again, the comparison test holds true for every value produced by the subquery.
- If the comparison test is FALSE for any data value in the column, then the ALL search condition returns FALSE. In this case, you can conclusively state that the comparison test does not hold true for every data value produced by the query.
- If the comparison test is not FALSE for any data value in the column, but it is NULL for one or more of the data values, then the ALL search condition returns NULL. In this situation, you cannot conclusively state whether there is a value produced by the subquery for which the comparison test does not hold true; there may or may not be, depending on the “actual” (but currently unknown) values for the NULL data.
The subtle errors that can occur when the ANY test is combined with the inequality (<>) comparison operator also occur with the ALL test. As with the ANY test, the ALL test can always be converted into an equivalent EXISTS test by moving the comparison inside the subquery.
 This article is excerpted from MySQL The Complete Reference by Vikram Vaswani (McGraw-Hill, 2003; ISBN 0072224770). Check it out at your favorite bookstore today. Buy this book now. |
Next: Subqueries and Joins >>
More MS SQL Server Articles
More By McGraw-Hill/Osborne
/ 19 
