How to Use ALL, SOME, and ANY in SQL Statements
ALL, SOME, and ANY can be confusing in SQL statements. Thousands of years ago, the Greek philosopher Aristotle formulated a system of logic that became the basis for much of Western thought. The essence of this logic is to start with a set of premises that you know to be true, apply valid operations, and, thereby, arrive at new truths.
An example of this procedure is as follows:
Premise 1: All Greeks are human.
Premise 2: All humans are mortal.
Conclusion: All Greeks are mortal.
Premise 1: Some Greeks are women.
Premise 2: All women are human.
Conclusion: Some Greeks are human.
By way of presenting a third example, consider the same logical idea of the second example in a slightly different way:
If any Greeks are women and all women are human, then some Greeks are human.
The first example uses the universal quantifier ALL in both premises, enabling you to make a sound deduction about all Greeks in the conclusion. The second example uses the existential quantifier SOME in one premise, enabling you to make a deduction about some Greeks in the conclusion. The third example uses the existential quantifier ANY, a synonym for SOME, to reach the same conclusion you reach in the second example.
Look at how SOME, ANY, and ALL apply in SQL.
Consider an example in baseball statistics. Baseball is a demanding sport, especially for pitchers. A pitcher must throw the baseball to home plate between 90 and 150 times during a game. This effort can be exhausting, and if the pitcher becomes ineffective before the game ends, a relief pitcher replaces him. Pitching an entire game is an outstanding achievement, regardless of whether the effort results in a victory.
Suppose you’re keeping track of the number of complete games that all major-league pitchers pitch. In one table, you list all the American League pitchers, and in another table, you list all the National League pitchers. Both tables contain the players’ first names, last names, and number of complete games pitched.
The American League permits a designated hitter (DH) (who isn’t required to play a defensive position) to bat in place of any of the nine players who play defense. The National League doesn’t allow designated hitters, but does allow pinch-hitters.
When the pinch-hitter comes into the game for the pitcher, the pitcher can’t play for the remainder of the game. Usually the DH bats for the pitcher, because pitchers are notoriously poor hitters. Pitchers must spend so much time and effort on perfecting their pitching that they don’t have as much time to practice batting as the other players do.
Suppose you have a theory that, on average, American League starting pitchers throw more complete games than do National League starting pitchers. This idea is based on your observation that designated hitters enable hard-throwing, weak-hitting, American League pitchers to keep pitching as long as they’re effective, even in a close game. Because a DH is already batting for these pitchers, their poor hitting isn’t a liability.
In the National League, however, under everyday circumstances the pitcher would go to bat. When trailing in the late innings, most managers would call for a pinch hitter to bat for the pitcher, judging that getting a base hit in this situation is more important than keeping an effective pitcher in the game. To test your theory, you formulate the following query:
SELECT FirstName, LastName FROM AMERICAN_LEAGUER WHERE CompleteGames > ALL (SELECT CompleteGames FROM NATIONAL_LEAGUER) ;
The subquery (the inner SELECT) returns a list showing, for every National League pitcher, the number of complete games he pitched. The outer query returns the first and last names of all American Leaguers who pitched more complete games than ALL of the National Leaguers.
The entire query returns the names of those American League pitchers who pitched more complete games than the pitcher who has thrown the most complete games in the National League.
Consider the following similar statement:
SELECT FirstName, LastName FROM AMERICAN_LEAGUER WHERE CompleteGames > ANY (SELECT CompleteGames FROM NATIONAL_LEAGUER) ;
In this case, you use the existential quantifier ANY instead of the universal quantifier ALL. The subquery (the inner, nested query) is identical to the subquery in the previous example. This subquery retrieves a complete list of the complete game statistics for all the National League pitchers.
The outer query returns the first and last names of all American League pitchers who pitched more complete games than ANY National League pitcher. Because you can be virtually certain that at least one National League pitcher hasn’t pitched a complete game, the result probably includes all American League pitchers who’ve pitched at least one complete game.
If you replace the keyword ANY with the equivalent keyword SOME, the result is the same. If the statement that at least one National League pitcher hasn’t pitched a complete game is a true statement, you can then say that SOME National League pitcher hasn’t pitched a complete game.