The keywords all and any modify a comparison operator that introduces a subquery.
When any is used with <, >, or = with a subquery, it returns results when any value retrieved in the subquery matches the value in the where or having clause of the outer statement.
When all is used with < or > in a subquery, it returns results when all values retrieved in the subquery match the value in the where or having clause of the outer statement.
The syntax for any and all is:
{where | having} [not]
expression comparison_operator {any | all} (subquery)
Using the > comparison operator as an example:
> all means greater than every value, or greater than the maximum value. For example, > all (1, 2, 3) means greater than 3.
> any means greater than at least one value, or greater than the minimum value. Therefore, > any (1, 2, 3) means greater than 1.
If you introduce a subquery with all and a comparison operator does not return any values, the entire query fails.
all and any can be tricky. For example, you might ask “Which books commanded an advance greater than any book published by New Age Books?”
You can paraphrase this question to make its SQL “translation” more clear: “Which books commanded an advance greater than the largest advance paid by New Age Books?” 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 = "New Age Books")
title ---------------------------------------- The Gourmet Microwave
For each title, the outer query gets the titles and advances from the titles table, and it compares these to the advance amounts paid by New Age Books returned from the subquery. The outer query looks at the largest value in the list and determines whether the title being considered has commanded an even greater advance.
The > all operator means that, for a row to satisfy the condition in the outer query, the value in the column that introduces the subquery must be greater than each of the values returned by the subquery.
For example, to find the books that are priced higher than the highest-priced book in the mod_cook category:
select title from titles where price > all (select price from titles where type = "mod_cook")
title --------------------------------------------------- But Is It User Friendly? Secrets of Silicon Valley Computer Phobic and Non-Phobic Individuals: Behavior Variations Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean (4 rows affected)
However, if the set returned by the inner query contains a NULL, the query returns 0 rows. This is because NULL stands for “value unknown,” and it is impossible to tell whether the value you are comparing is greater than an unknown value.
For example, try to find the books that are priced higher than the highest-priced book in the popular_comp category:
select title from titles where price > all (select price from titles where type = "popular_comp")
title --------------------------------------------------- (0 rows affected)
No rows are returned because the subquery finds that one of the books, Net Etiquette, has a null price.
The = all operator means that for a row to satisfy the outer query the value in the column that introduces the subquery must be the same as each value in the list of values returned by the subquery.
For example, the following query identifies the authors who live in the same city at the postal code:
select au_fname, au_lname, city from authors where city = all (select city from authors where postalcode like "946%")
> any means that, for a row to satisfy 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 returned by the subquery.
The following example is introduced with a comparison operator modified by any. It finds each title that has an advance larger than any advance amount paid by New Age Books.
select title from titles where advance > any (select advance from titles, publishers where titles.pub_id = publishers.pub_id and pub_name = "New Age Books")
title --------------------------------------------------- The Busy Executive’s Database Guide Cooking with Computers: Surreptitious Balance Sheets You Can Combat Computer Stress! Straight Talk About Computers The Gourmet Microwave But Is It User Friendly? Secrets of Silicon Valley Computer Phobic and Non-Phobic Individuals: Behavior Variations Is Anger the Enemy? Life Without Fear Emotional Security: A New Algorithm Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean Fifty Years in Buckingham Palace Kitchens Sushi, Anyone?
For each title selected by the outer query, the inner query finds a list of advance amounts paid by New Age Books. The outer query looks at all the values in the list and determines whether the title being considered has commanded an advance that is larger than any of those values. In other words, this example finds titles with advances as large as or larger than the lowest value paid by New Age Books.
If the subquery does not return any values, the entire query fails.
The = any operator is an existence check; it is equivalent to in. For example, to find authors that live in the same city as any publisher, you can use either = any or in:
select au_lname, au_fname from authors where city = any (select city from publishers) select au_lname, au_fname from authors where city in (select city from publishers)
au_lname au_fname -------------- -------------- Carson Cheryl Bennet Abraham
However, the != any operator is different from not in. The != any operator means “not = a or not = b or not = c”; not in means “not = a and not = b and not = c”.
For example, to find the authors who live in a city where no publisher is located:
select au_lname, au_fname from authors where city != any (select city from publishers)
The results include all 23 authors. This is because every author lives in some city where no publisher is located, and each author lives in only one city.
The inner query finds all the cities in which publishers are located, and then, for each city, the outer query finds the authors who do not live there.
Here is what happens when you substitute not in in the same query:
select au_lname, au_fname from authors where city not in (select city from publishers)
au_lname au_fname -------------- ------------ White Johnson Green Marjorie O’Leary Michael Straight Dick Smith Meander Dull Ann Gringlesby Burt Locksley Chastity Greene Morningstar Blotchet-Halls Reginald Yokomoto Akiko del Castillo Innes DeFrance Michel Stringer Dirk MacFeather Stearns Karsen Livia Panteley Sylvia Hunter Sheryl McBadden Heather Ringer Anne Ringer Albert
These are the results you want. They include all the authors except Cheryl Carson and Abraham Bennet, who live in Berkeley, where Algodata Infosystems is located.
You get the same results if you use !=all, which is equivalent to not in:
select au_lname, au_fname from authors where city != all (select city from publishers)
