Subqueries that return one value vs. multiple values

  • michael.leach2015

    SSCommitted

    Points: 1584

    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:

      SELECT	ord_no, purch_amt
    FROM orders o
    WHERE salesman_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?

     

  • Mr. Brian Gale

    SSC-Insane

    Points: 22759

    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.

  • Jeff Moden

    SSC Guru

    Points: 996436

    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)
    Forum FAQ

  • michael.leach2015

    SSCommitted

    Points: 1584

    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: 1584

    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: 996436

    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)
    Forum FAQ

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

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