BlackWaspTM

This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

SQL Server
SQL 2005+

SQL Server All, Some and Any Operators

The thirty-sixth part of the SQL Server Programming Fundamentals tutorial completes the review of the Transact-SQL (T-SQL) set of operators. This article describes the ALL, SOME and ANY operators, which are used to compare scalar values with column data.

Logical Operators

SQL Server provides a group of operators known collectively as the logical operators. They are used to test the truth of an expression and always return a Boolean result of true, false or unknown. During the course of the SQL Server Programming Fundamentals tutorial we have seen the use of a number of logical operators. In the Basic T-SQL Queries article the AND, OR, NOT, BETWEEN and LIKE logical operators where described. We have also seen the EXISTS an IN operators whilst examining the use of subqueries.

In this last article describing operators we will examine three new logical operators. These allow a scalar value to be compared with a list of values retrieved by a single-column query or subquery. Instead of performing a single comparison, an expression is generated for every row from the query's results. A collective decision as to the truth of the entire expression is then made.

Comparing Values to Column Data

This article's examples can be executed against the JoBS database, which has been created throughout the course of the tutorial. If you do not have an up-to-date copy of the database, download the script using the link at the top of the page. Execute the script to generate the database and populate it with test data.

ALL

The first of the operators that we will consider is ALL. Used with one of the comparison operators, ALL compares a single value against a set of data from a query. Each value from the query's results is combined with the scalar value to generate a single scalar expression. If all of the scalar expressions evaluate to true then the result of the ALL expression will be true. Otherwise the result will be false, or unknown if the results of the expressions cannot be determined.

To demonstrate we can execute a query against the Engineers table in the JoBS database. Let's assume that a business rule is to be implemented that specifies that no engineer may have an overtime rate lower than another engineer's basic hourly pay. We can use ALL with NOT to make this comparison as follows:

SELECT
    EngineerId,
    EngineerName,
    OvertimeRate
FROM
    Engineers
WHERE
    NOT OvertimeRate >= ALL (SELECT HourlyRate FROM Engineers)

Executing this query reveals that there are two engineers whose overtime rate is lower than permitted.

SOME and ANY

The SOME and ANY operators provide equivalent functionality. As with the ALL operator, each creates a series of scalar expressions based upon a single value, a comparison operator and the results of a single-column query. The difference is that the SOME and ANY operators return true if at least one of the generated expressions evaluates as true.

These operators allow us to modify the previous example query so that the NOT is no longer required. Instead, we can ask for the list of engineers who have an overtime rate that is lower than any other engineer's basic hourly rate, as follows:

SELECT
    EngineerId,
    EngineerName,
    OvertimeRate
FROM
    Engineers
WHERE
    OvertimeRate < ANY (SELECT HourlyRate FROM Engineers)

As the ANY and SOME operators are equivalent, you can interchange the two keywords without affecting the results:

SELECT
    EngineerId,
    EngineerName,
    OvertimeRate
FROM
    Engineers
WHERE
    OvertimeRate < SOME (SELECT HourlyRate FROM Engineers)

Operator Precedence

We can now add all of the logical operators, including those seen in previous articles, to the table of precedence.

Unary Bitwise Operators
~
Arithmetic Operators
* / %
Unary Arithmetic Operators
+ -
Arithmetic / Concatenation Operators
[+ arithmetic] [+ concatenation)] -
Bitwise Operators
& ^ |
Comparison Operators
=, >, <, >=, <=, <>, !=, !>, !<
Logical
NOT AND ALL ANY BETWEEN IN LIKE OR SOME
28 September 2009