Friday, February 5, 2010

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
-- 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.

No comments:

Post a Comment