SQL Server ANY Operator Explained

In SQL Server, you can use the ANY logical operator to compare a scalar value with a single-column set of values returned by a subquery.

It can be used with subqueries that have a result set of one column.

The ANY operator is the equivalent of the SOME logical operator.

Example

Assume we have two tables; Cats and Dogs

Cats

+---------+-----------+
| CatId   | CatName   |
|---------+-----------|
| 1       | Meow      |
| 2       | Fluffy    |
| 3       | Scratch   |
+---------+-----------+

Dogs

+---------+-----------+
| DogId   | DogName   |
|---------+-----------|
| 1       | Fetch     |
| 2       | Fluffy    |
| 3       | Wag       |
+---------+-----------+

Now let’s run a subquery using the ANY operator.

SELECT 
    CatId,
    CatName
FROM Cats c 
WHERE c.CatName = ANY (SELECT DogName FROM Dogs);

Result:

+---------+-----------+
| CatId   | CatName   |
|---------+-----------|
| 2       | Fluffy    |
+---------+-----------+

ANY vs SOME

We would get the same result if we were to change the query around so that it uses the SOME operator instead of ANY.

Here it is using the SOME operator instead of ANY.

SELECT 
    CatId,
    CatName
FROM Cats c 
WHERE c.CatName = SOME (SELECT DogName FROM Dogs);

Result:

+---------+-----------+
| CatId   | CatName   |
|---------+-----------|
| 2       | Fluffy    |
+---------+-----------+

ANY vs IN

We could also use the IN operator to get the same result.

SELECT 
    CatId,
    CatName
FROM Cats c 
WHERE c.CatName IN (SELECT DogName FROM Dogs);

Result:

+---------+-----------+
| CatId   | CatName   |
|---------+-----------|
| 2       | Fluffy    |
+---------+-----------+

ANY vs EXISTS

We could do the same thing with the EXISTS operator.

SELECT 
    CatId,
    CatName
FROM Cats c 
WHERE EXISTS (SELECT DogId, DogName FROM Dogs d
WHERE c.CatName = d.DogName);

Result:

+---------+-----------+
| CatId   | CatName   |
|---------+-----------|
| 2       | Fluffy    |
+---------+-----------+

Error 116?

If you get error 116 when using ANY, it’s probably because you’re selecting multiple columns in your subquery. The ANY operator can only be used with subqueries that have a result set of one column.

Here’s an example of how we can cause this error.

SELECT 
    CatId,
    CatName
FROM Cats c 
WHERE c.CatName = ANY (SELECT * FROM Dogs);

Result:

Msg 116, Level 16, State 1, Line 5
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

In this sense, the ANY operator is more similar to the IN operator than it is to the EXISTS operator. We would get the same error with IN.

If you must return multiple columns in your subquery, use EXISTS.