Greg Low recently wrote posts on the ALL as well as the ANY and SOME SQL operators. I’ve seen these operators but I can’t say that I’ve used them before.
All three are logical operators, so they return a boolean value as their result. Other logical operators include AND, IN and EXISTS.
For ALL, we’ll compare a value to the results of a subquery, and the comparison must be true for all values from the subquery in order to return TRUE. For example:
<br> IF (('2021-03-31') > ALL(SELECT * FROM (VALUES ('2021-03-30'), ('2021-03-15'), ('2021-03-01')) as t(CompareDate)))<br> SELECT 'TRUE' ELSE SELECT 'FALSE';<br>
There are also ANY and SOME operators, these two do the same thing. They’ll return a TRUE if a comparison returns a true for any of the values from the subquery. We can tweak the ALL example to demonstrate:
<br> IF (('2021-03-31') > SOME(SELECT * FROM (VALUES ('2021-04-15'), ('2021-03-15'), ('2021-04-01')) as t(CompareDate)))<br> SELECT 'TRUE' ELSE SELECT 'FALSE';<br>
I’m not sure why we have both the ANY and SOME operators. I did find this SQL Shack article that mentioned ANY was in use first. SOME does seem to me a bit clearer than ANY.
Not sure these operators are something that would be used a great deal, but it is a good thing to know about, in case a good use were to come up.
Microsoft: Logical Operators