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 SQL-92 standard equivalent for ANY.

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.

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, that is, greater than the minimum. So >ANY (1, 2, 3) means greater than 1.

For a row in a subquery with >ALL to satisfy the condition specified in the outer query, the value in the column introducing 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.


Note This example can be run many different ways, as long as the inner query returns only one value.


USE pubs

-- Option 1 using MAX in the inner query

SELECT title

FROM titles

HAVING MAX(advance) > ALL

WHERE advance > ALL

    (

     SELECT MAX(advance)

     FROM publishers INNER JOIN titles ON

        titles.pub_id = publishers.pub_id

     WHERE pub_name = 'Algodata Infosystems'

    )

  

-- Option 2 using GROUP BY and HAVING and no ALL

USE pubs

SELECT title

FROM titles

GROUP BY title

HAVING MAX(advance) >

    (

     SELECT MAX(advance)

     FROM publishers INNER JOIN titles ON

        titles.pub_id = publishers.pub_id

     WHERE pub_name = 'Algodata Infosystems'

    )

  

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

USE pubs

SELECT title

FROM titles

WHERE advance > ANY

    (SELECT advance

    FROM publishers INNER JOIN titles

    ON titles.pub_id = publishers.pub_id

        AND pub_name = 'Algodata Infosystems')

  

Here is the result set:

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.

USE pubs

SELECT au_lname, au_fname

FROM authors

WHERE city IN

    (SELECT city

    FROM publishers)

  

USE pubs

SELECT au_lname, au_fname

FROM authors

WHERE city = ANY

    (SELECT city

    FROM publishers)

  

Here is the result set for either query:

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, this query finds the authors who live in a city in which no publisher is located.

USE pubs

SELECT au_lname, au_fname

FROM authors

WHERE city <> ANY

    (SELECT city

    FROM publishers)

  

Here is the result set:

au_lname                                 au_fname            

---------------------------------------- --------------------

White                                    Johnson             

Green                                    Marjorie            

Carson                                   Cheryl              

O'Leary                                  Michael             

Straight                                 Dean                

Smith                                    Meander             

Bennet                                   Abraham             

Della Buena                              Ann                 

Gringlesby                               Burt                

Locksley                                 Charlene            

Greene                                   Morningstar         

Blotchet-Halls                           Reginald            

Yokomoto                                 Akiko               

del Covello                              Innes               

DeFrance                                 Michel              

Stringer                                 Dirk                

MacFeather                               Stearns             

Karsen                                   Livia               

Panteley                                 Sylvia              

Hunter                                   Sheryl              

McBadden                                 Heather             

Ringer                                   Anne                

Ringer                                   Albert              

  

(23 row(s) affected)

  

The results include all 23 authors because every author lives in a city in which one or more of the publishers is not located. 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 use 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.

USE pubs

SELECT au_lname, au_fname

FROM authors

WHERE city NOT IN

    (SELECT city

    FROM publishers)

  

Here is the result set:

au_lname                                 au_fname            

---------------------------------------- --------------------

White                                    Johnson             

Green                                    Marjorie            

O'Leary                                  Michael             

Straight                                 Dean                

Smith                                    Meander             

Della Buena                              Ann                 

Gringlesby                               Burt                

Locksley                                 Charlene            

Greene                                   Morningstar         

Blotchet-Halls                           Reginald            

Yokomoto                                 Akiko               

del Covello                              Innes               

DeFrance                                 Michel              

Stringer                                 Dirk                

MacFeather                               Stearns             

Karsen                                   Livia               

Panteley                                 Sylvia              

Hunter                                   Sheryl              

McBadden                                 Heather             

Ringer                                   Anne                

Ringer                                   Albert              

  

(21 row(s) affected)

  

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

USE pubs

SELECT au_lname, au_fname

FROM authors

WHERE city <> ALL

    (SELECT city

    FROM publishers)

  

  


(c) 1988-98 Microsoft Corporation. All Rights Reserved.