SQL Server SOME Operator Explained

In SQL Server, you can use the SOME 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 SOME operator is the equivalent of the ANY logical operator.

Example

Imagine 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 = SOME (SELECT DogName FROM Dogs);

Result:

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

SOME vs ANY

As mentioned, SOME is the equivalent of ANY.

So we could change our query to use ANY instead of SOME.

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

Result:

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

SOME vs IN

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

Here it is using the IN operator.

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

Result:

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

SOME vs EXISTS

We could do the same thing with the EXISTS operator.

Here it is using 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 SOME, it’s probably because you’re selecting multiple columns in your subquery. The SOME 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 = SOME (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.

We would get the same error with IN. If you must return multiple columns in your subquery, use EXISTS.