The Logical Operator Less Used

Most people who need to work with SQL Server databases will be familiar with the basic logical operators such as AND, OR, BETWEEN, IN. A good number might also be across the use of the EXISTS operator. Perhaps less known are three others ALL, ANY and SOME.

ALL will return true if all of a set of comparisons are true.
ANY will return true if any of a set of comparisons are true.
SOME will return true if some of a set of comparisons are true.

There are numerous areas where these operators can be used. One case is where there is the need to compare a value against a range of other values from . It can take away the need for a string of ANDs and ORs in WHERE clause.

The syntax is along the lines of:

IF @valueToCheck <> ALL (SELECT referenceValue FROM referenceTable)

-- do something

ELSE

-- do something else

In this case the first code branch will be executed only if the valueToCheck is less than all of the values in the reference table.