Subqueries that return more than one value :

A very frequent request is to retrieve a value based on some correlation with the main query table. For example, consider the following two tables, storing details of products and the plants that manufacture these products:

Products table:

sku product_description
----- ------------------
1 Bike
2 Ball
3 Phone

ProductPlants table:

sku plant_nbr
----- -----------
1 1
2 1
3 2

The request is to extract the manufacturing plant for each product. One way to satisfy the request is to write the following query using correlated subquery to retrieve the plant:

SELECT sku, product_description,
(SELECT plant_nbr
FROM ProductPlants AS B
WHERE B.sku = A.sku) AS plant_nbr
FROM Products AS A;

Note that the point here is to illustrate a technique; there could be a more efficient way to accomplish the same task. However, all works fine and we get the correct result set:

sku product_description plant_nbr
---- ------------------- -----------
1 Bike 1
2 Ball 1
3 Phone 2

The query will continue to work happily until the day arrives that the company decides to start manufacturing Balls at plant 3, to cope with increasing demand. The ProductPlants table now looks like this:

sku plant_nbr
----- -----------
1 1
2 1
2 3
3 2

All of a sudden, our query starts generating the following error:

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.

The error is descriptive enough. Instead of the expected scalar value, our subquery returns a result set, which breaks the query. Based on our business requirements, the fix is simple. To list all plants manufacturing plant for a particular product, we simply use a JOIN:

SELECT A.sku, A.product_description, B.plant_nbr
FROM Products AS A
JOIN ProductPlants AS B
ON A.sku = B.sku;

Now the query completes without errors and returns the correct results:

sku product_description plant_nbr
---- -------------------- -----------
1 Bike 1
2 Ball 1
2 Ball 3
3 Phone 2

Note that the same error can occur in a predicate where a column or expression is tested against a subquery, for example "… column = (SELECT value FROM Table)". In that case, the solution is to use the IN predicate in place of "=".