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)