Subqueries that return one value vs. multiple values

Question

Subqueries that return one value vs. multiple values

michael.leach2015

SSCommitted

Points: 1597
More actions
May 22, 2020 at 8:59 pm

#3756947
I am testing two examples in which a WHERE clause is compared to the results of a subquery.
```
  SELECT BusinessEntityID, FirstName, LastName
  FROM Person.Person p
  WHERE p.BusinessEntityID = (SELECT BusinessEntityID FROM HumanResources.Employee h WHERE p.BusinessEntityID = h.BusinessEntityID);
```
This first example works. It returns 290 rows. This means that p.BusinessEntity in the outer WHERE clause can handle multiple values.
Then there is this example:
```
  SELECTord_no, purch_amt
  FROMorders o
  WHEREsalesman_id = (SELECT Salesman_id FROM salesman);
```
When I run this 2nd example, I get an error stating:
```
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
```
This 2nd example is also returning multiple value (I tested that subquery by itself), but this time I get an error.
Why is it that the WHERE clause in the first example can handle multiple values, but the 2nd example can't?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Mr. Brian Gale SSC-Insane Points: 24675 More actions · Answer 1

As a guess on your data, it isn't returning multiple rows in the first select subquery.

I am guessing that in the table HumanResources.Employee, the BusinessEntityID column is unique. So p.BusinessEntityID of 5 (for example) will match 1 and only 1 row in the subquery.

In the second one, you are likely getting more than 1 row back and therefore you get the error.

When using an equality comparison, it NEEDS to be a 1 to 1 mapping (ie 1 value = 1 value).

Lets take an example that everyone can run (using master database). The following will give an error:

USE [master];
GO
SELECT
[A].[object_id]
, [A].[name]
 FROM[sys].[all_columns] AS [A]
 WHERE[A].[column_id] =
 (
 SELECT
.[column_id]
  FROM[sys].[all_columns] AS 
  WHERE .[object_id] = 3
 );

because I am comparing 1 value to multiple. BUT if I change the query to:

USE [master];
GO
SELECT
[A].[object_id]
, [A].[name]
 FROM[sys].[all_columns] AS [A]
 WHERE[A].[column_id] =
 (
 SELECT
.[column_id]
  FROM[sys].[all_columns] AS 
  WHERE [A].[name]= .[name]
AND .[object_id] = 3
 );

We are now getting a 1 to 1 mapping of Column ID. When A.Name = B.Name AND b.Object_ID is 3, our column_ID is returning 1 row per name even though I am getting multiple rows back. The comparison is 1 to 1 in example 2 and 1 to many in example 1.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 2

Again... teaching a man to fish. 😀

If you do a search for "subqueries in sql server" and look for the "Microsoft Docs" (BOL) entry in the returns, you'll end up on the following page...

https://docs.microsoft.com/en-us/sql/relational-databases/performance/subqueries?view=sql-server-ver15

With that page, search for the heading titled "Subqueries with Comparison Operators" and you find a bit more detail on the subject. It unfortunately only explains why a single value return is required in that section but provides no example of your first query. However, if you read the rest of the article (and you should because it's a hugely important concept), you find the section title "Correlated subqueries" that explains it a little better. You'll also learn about (NOT) IN and (NOT) EXISTS, which are also powerful tools.

Now, almost no documentation is perfect. One of the biggest faults with that article is that they don't explain "multi return" correlated subqueries that can actually be used for "Relational Multiplication" (kind of like a CROSS JOIN or other many-to-many criteria) nor do they even provide a link. Since, that too, is an incredibly important concept (especially when it comes to the creation of test and other data without RBAR loops), you should read about "APPLY" (two flavors, CROSS APPLY and OUTER APPLY).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

michael.leach2015 SSCommitted Points: 1597 More actions · Answer 3

Brian,

Yes, you're right. In that first example I had it would return only one row instead of many. I can't believe I overlooked that. Many times, when I get an answer on a question, I realize that the answer was right in front of me. I spend many hours thinking through all of this stuff, but occasionally I miss the obvious things (I guess everybody does once in a while).

michael.leach2015 SSCommitted Points: 1597 More actions · Answer 4

Jeff Moden wrote:

Again... teaching a man to fish. 😀
If you do a search for "subqueries in sql server" and look for the "Microsoft Docs" (BOL) entry in the returns, you'll end up on the following page...
https://docs.microsoft.com/en-us/sql/relational-databases/performance/subqueries?view=sql-server-ver15
With that page, search for the heading titled "Subqueries with Comparison Operators" and you find a bit more detail on the subject. It unfortunately only explains why a single value return is required in that section but provides no example of your first query. However, if you read the rest of the article (and you should because it's a hugely important concept), you find the section title "Correlated subqueries" that explains it a little better. You'll also learn about (NOT) IN and (NOT) EXISTS, which are also powerful tools.
Now, almost no documentation is perfect. One of the biggest faults with that article is that they don't explain "multi return" correlated subqueries that can actually be used for "Relational Multiplication" (kind of like a CROSS JOIN or other many-to-many criteria) nor do they even provide a link. Since, that too, is an incredibly important concept (especially when it comes to the creation of test and other data without RBAR loops), you should read about "APPLY" (two flavors, CROSS APPLY and OUTER APPLY).

Another great reply. Thank you. Interesting that you mentioned CROSS APPLY and OUTER APPLY because that is just what I started yesterday.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 5

On that note, Paul White wrote some great articles on both. You can find them below...

https://www.sqlservercentral.com/articles/understanding-and-using-apply-part-1

https://www.sqlservercentral.com/articles/understanding-and-using-apply-part-2

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)