Comparison Operators Modified by ANY, SOME, or ALL

Comparison operators that introduce a subquery can be modified by the keywords ALL or ANY. SOME is an ANSI-standard keyword for ANY.

Note The =ALL construct is not supported since it would result in an error whenever the subquery resulted in multiple rows.

Subqueries introduced with a modified comparison operator return a list of zero or more values and can include a GROUP BY or HAVING clause. These subqueries can be restated with EXISTS, as discussed later in this chapter.

Using the > comparison operator as an example, >ALL means greater than every value ¾ in other words, greater than the maximum value. For example, >ALL (1, 2, 3) means greater than 3. >ANY means greater than at least one value ¾ in other words, greater than the minimum. So >ANY (1, 2, 3) means greater than 1.

In the context of a subquery, >ALL means that for a row to satisfy the condition specified in the outer query, the value in the column that introduces the subquery must be greater than each value in the list of values returned by the subquery.

Similarly, >ANY means that for a row to satisfy the condition specified in the outer query, the value in the column that introduces the subquery must be greater than at least one of the values in the list of values returned by the subquery.

The use of ALL and ANY can be confusing because computers cannot tolerate the ambiguity that these words sometimes have in English. For example, you might ask the question, "Which books commanded an advance greater than any book published by Algodata Infosystems?"

This question can be paraphrased to make its SQL translation clearer: "Which books commanded an advance greater than the largest advance paid by Algodata Infosystems?" The ALL keyword (not the ANY keyword) is required here:

SELECT title
FROM titles
WHERE advance > ALL
    (SELECT advance
    FROM publishers, titles
    WHERE titles.pub_id = publishers.pub_id
        AND pub_name = 'Algodata Infosystems')

For each title, the inner query finds a list of advance amounts paid by Algodata. The outer query looks at the largest value in the list and determines whether the title currently being considered has commanded an even bigger advance.

If a subquery introduced with ALL and a comparison operator does not return any values, all rows in outer query satisfy the condition.

The following query provides an example of a subquery introduced with a comparison operator modified by ANY. It finds the titles that got an advance larger than the minimum advance amount paid by Algodata Infosystems.

SELECT title
FROM titles
WHERE advance > ANY
    (SELECT advance
    FROM publishers, titles
    WHERE titles.pub_id = publishers.pub_id
        AND pub_name = 'Algodata Infosystems')

title
---------------------------------------------------------------
You Can Combat Computer Stress!
The Gourmet Microwave
But Is It User Friendly?
Secrets of Silicon Valley
Computer Phobic and Non-Phobic Individuals: Behavior Variations
Life Without Fear
Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean
Sushi, Anyone?

(8 row(s) affected)

For each title, the inner query finds a list of advance amounts paid by Algodata. The outer query looks at all values in the list and determines whether the title currently being considered has commanded an advance larger than any of those amounts. In other words, it finds titles with advances as large or larger than the lowest value paid by Algodata.

If the subquery does not return any values, the entire query fails to return any values.

The =ANY operator is equivalent to IN. For example, to find authors who live in the same city as a publisher, you can use either IN or =ANY:

SELECT au_lname, au_fname
FROM authors
WHERE city IN
    (SELECT city
    FROM publishers)

SELECT au_lname, au_fname
FROM authors
WHERE city = ANY
    (SELECT city
    FROM publishers)

au_lname            au_fname
--------            ---------
Carson            Cheryl
Bennet            Abraham

(2 row(s) affected)

The < >ANY operator, however, differs from NOT IN: < >ANY means not = a, or not = b, or not = c. NOT IN means not = a, and not = b, and not = c. <>ALL means the same as NOT IN.

For example, say you want to find the authors who live in a city where no publisher is located. You might mistakenly try this query:

SELECT au_lname, au_fname
FROM authors
WHERE city <> any
    (SELECT city
    FROM publishers)

The results include all 23 authors because every author lives in a city where one or more of the publishers is not located. What's happened is that the inner query finds all the cities in which publishers are located, and then, for each city, the outer query finds the authors who don't live there.

However, when you substitute NOT IN in this query, the results include all the authors except Cheryl Carson and Abraham Bennet, who live in Berkeley, where Algodata Infosystems is located.

SELECT au_lname, au_fname
FROM authors
WHERE city NOT IN
    (SELECT city
    FROM publishers)

You can get the same results with the < >ALL operator, which is equivalent to NOT IN:

SELECT au_lname, au_fname
FROM authors
WHERE city <> ALL
    (SELECT city
    FROM publishers)