SQL Series...35

The ALL, SOME, and ANY quantifiers

Another way to make sure that a subquery returns a single value is to intro-duce it with a quantified comparison operator. The universal quantifier ALL

and the existential quantifiers SOME and ANY, when combined with a comparison operator, process the list returned by a subquery, reducing it to a single value.

The contents of the two tables are given by the following two queries:

SELECT * FROM NATIONAL

FirstName	LastName	CompleteGames
---------	--------	-------------
Sal	Maglie	11
Don	Newcombe	9
Sandy	Koufax	13
Don	Drysdale	12
Bob	Turley	8
SELECT * FROM AMERICAN

FirstName	LastName	CompleteGames
---------	--------	-------------
Whitey	Ford	12
Don	Larson	10
Bob	Turley	8
Allie	Reynolds	14

The theory is that the pitchers with the most complete games should be in the American League because of the presence of designated hitters in that league. One way to verify this theory is to build a query that returns all American League pitchers who have thrown more complete games than all the National League pitchers. The query can be formulated as follows:

SELECT *

FROM AMERICAN

WHERE CompleteGames > ALL

(SELECT CompleteGames FROM NATIONAL) ;

This is the result:

FirstName LastName CompleteGames

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

Allie        Reynolds             14

The subquery (SELECT CompleteGames FROM NATIONAL) returns the values in the CompleteGames column for all National League pitchers. The > ALL quantifier says to return only those values of CompleteGames in the AMERICAN table that are greater than each of the values returned by the subquery. This condition translates into “greater than the highest value returned by the subquery.” In this case, the highest value returned by the subquery is 13 (Sandy Koufax). The only row in the AMERICAN table higher than that is Allie Reynolds’s record, with 14 complete games.

What if your initial assumption was wrong? What if the major league leader in complete games was a National League pitcher, in spite of the fact that the National League has no designated hitter? If that were the case, the query

SELECT *

FROM AMERICAN

WHERE CompleteGames > ALL

(SELECT CompleteGames FROM NATIONAL) ;

would return a warning stating that no rows satisfy the query’s conditions, meaning that no American League pitcher has thrown more complete games than the pitcher who has thrown the most complete games in the National League.