Understanding the ANY and ALL Comparison Modifiers

When you use a Where or Having clause to create a subquery that returns zero to many values, you modify a comparison operator (such as equal to, less than, greater than) with the ANY or ALL comparison modifiers. Consider the following two examples:

>ALL—This expression means greater than every value. You can also think of this as greater than the largest value.
>ANY—This expression means greater than at least one value. You can also think of this as greater than the minimum value.

Problems arise with ANY and ALL in the following two cases:

The subquery comes back empty—If the subquery is empty, ALL is automatically true and ANY is automatically false.
The subquery returns null values—When the subquery returns NULL values, technically, the result is unknown. Remember that you are using comparison operators, and a comparison to NULL is undefined. For the most part, SQL treats unknown the same as false for Where clauses.