Result values
If the subselect returns an empty set, ALL
returns true
and ANY
SOME
return false
, even if the lefthand side expression is NULL
.This follows from the definitions and the rules of formal logic.(Mathheads will already have noticed that ALL
is equivalent to the universal (“A”) quantifier and ANY
SOME
to the existential (“E”) quantifier.)
For nonempty sets, you can write out “A <op> {ANYSOME} (<subselect>)
” as
A <op> E1 or A <op> E2 or ... or A <op> En
with <op>
the operator used and E1
, E2
etc. the items returned by the subquery.
Likewise, “A <op> ALL (<subselect>)
” is the same as
A <op> E1 and A <op> E2 and ... and A <op> En
This should look familiar.The first writeout is equal to that of the IN
predicate, except that the operator may now be something other than ‘=
’.The second is different but has the same general form.We can now work out how nullness of A and/or nullness of subselect results affect the outcome of ANY
SOME
and ALL
.This is done in the same way as earlier with IN
, so instead of including all the steps here we will just present the result tables.Again, read the questions in the left column from top to bottom.As soon as you answer a question with “Yes”, read the result from the second column and you’re done.
Conditions 
Result 



Does the subselect return an empty set? 

Else, is 

Else, does at least one comparison return 

Else, does at least one comparison return 

Else (i.e. all comparisons return 

If you think these results look a lot like what we saw with IN()
, you’re right: with the ‘=
’ operator, ANY
is the same as IN
.In the same way, “<> ALL
” is equivalent to NOT IN
.
Warning

Bug alert (revisited)
In versions before 2.0, “ On the bright side, “ 
Conditions 
Result 



Does the subselect return an empty set? 

Else, is 

Else, does at least one comparison return 

Else, does at least one comparison return 

Else (i.e. all comparisons return 

Warning

ALL bugAlthough “ 
Note

Strictly speaking, the second question in both tables (“is The reason we included those questions is convenience: you can see if a set is empty at a glance, and it’s also easier to check if the lefthand side expression is 