If you get error Msg 512 that reads “Subquery returned more than 1 value…” in SQL Server, it’s because you’re using a subquery that returns more than one value in a scenario where this is not allowed.

Example of Error

Suppose we have the following two tables:

SELECT * FROM Dogs;

SELECT * FROM Cats;

Result:

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

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

And we run the following query against those two tables:

SELECT * FROM Dogs 
WHERE DogName = ( SELECT CatName FROM Cats );

Result:

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

We can see that it resulted in error Msg 512.

This error message explicitly tells us that the “Subquery returned more than 1 value” and that “This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression”.

The solution to this will depend on what you’re trying to do in the query. Below are a couple of options for resolving this issue.

Solution 1

One way to deal with this is to use a different operator. What I mean is, use an operator other than =, !=, <, <= , >, or >=.

Here’s an example that uses the IN operator:

SELECT * FROM Dogs 
WHERE DogName IN ( SELECT CatName FROM Cats );

Result:

+---------+-----------+
| DogId   | DogName   |
|---------+-----------|
| 2       | Fluffy    |
| 4       | Fluffy    |
+---------+-----------+

Solution 2

Another option is to keep the equals (=) operator (or whichever operator is in the original query), but change the subquery.

Here’s an example of changing the subquery, while keeping the equals operator:

SELECT * FROM Dogs 
WHERE DogName = ( SELECT CatName FROM Cats WHERE CatId = 2 );

Result:

+---------+-----------+
| DogId   | DogName   |
|---------+-----------|
| 2       | Fluffy    |
| 4       | Fluffy    |
+---------+-----------+

In this case the subquery returned one value only, and the equals operator was fine with that.

Solution 3

Note that the above subqueries only return one column. If the subqueries returned multiple columns, we’d need to change the outer query so that it uses the EXISTS operator.

Example:

SELECT * FROM Dogs d
WHERE EXISTS ( SELECT * FROM Cats c WHERE c.CatName = d.DogName );

Result:

+---------+-----------+
| DogId   | DogName   |
|---------+-----------|
| 2       | Fluffy    |
| 4       | Fluffy    |
+---------+-----------+

If we didn’t change it to use the EXISTS operator, then we’d probably get error message 116.