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
.