SET vs. SELECT

  • sergey.gigoyan

    Ten Centuries

    Points: 1109

    Comments posted to this topic are about the item SET vs. SELECT

  • twin.devil

    SSC-Insane

    Points: 22208

    very nice question and good knowledge base. Thanks for sharing

  • Carlo Romagnano

    SSC-Insane

    Points: 21748

    Easy, thanks!

    😀

  • Koen Verbeeck

    SSC Guru

    Points: 258928

    Very good to know. Thanks for the question.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Emil B

    SSCertifiable

    Points: 5568

    Very good question!

  • This was removed by the editor as SPAM

  • Simon Jackson

    SSC Rookie

    Points: 39

    The answer is right, but I'm not convinced about the reasoning.

    If I replace the SET with SELECT , I get the same result.

    Just from experimenting, it seems to do with what the the sub query in brackets is returning.

    e.g.

    SELECT @a = (SELECT 1 WHERE 1=2) -- will assign NULL

    SELECT @a = (SELECT 1) -- will assign 1

    Thoughts anyone?

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    Simon Jackson (7/14/2015)


    The answer is right, but I'm not convinced about the reasoning.

    If I replace the SET with SELECT , I get the same result.

    Just from experimenting, it seems to do with what the the sub query in brackets is returning.

    e.g.

    SELECT @a = (SELECT 1 WHERE 1=2) -- will assign NULL

    SELECT @a = (SELECT 1) -- will assign 1

    Thoughts anyone?

    Your first example is more convoluted than what this question covers. (And, frankly, more convoluted than what I normally see when I get called in at a customer).

    Most people use either

    SELECT @a = x FROM SomeTable WHERE 1 = 2;

    or

    SET @a = (SELECT x FROM SomeTable WHERE 1 = 2);

    Here, the SELECT will not change @a and the SET will set it to NULL.

    Also, if the WHERE clause would return multiple rows, the SET will fail with an error (alerting you to a probably incorrect WHERE clause), whereas the SELECT will simply select one of the rows that was returned and use that value, without any alert that this happened.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • wedneyyuri

    SSC Veteran

    Points: 234

    Good explanation, I never had this kind of problem.

  • wedneyyuri

    SSC Veteran

    Points: 234

    Good explanation, I never had this kind of problem.

  • sknox

    SSChampion

    Points: 12224

    Simon Jackson (7/14/2015)


    The answer is right, but I'm not convinced about the reasoning.

    If I replace the SET with SELECT , I get the same result.

    Just from experimenting, it seems to do with what the the sub query in brackets is returning.

    e.g.

    SELECT @a = (SELECT 1 WHERE 1=2) -- will assign NULL

    SELECT @a = (SELECT 1) -- will assign 1

    Thoughts anyone?

    It's not what the subquery returns, but if the subquery returns anything.

    When you place a subquery in a scalar expression you're requiring that SQL Server convert a result set into a scalar value.

    If there's a single result, no problem.

    If there are multiple results, SQL Server fails with an error.

    If there's no result, SQL Server returns NULL.

    However, if you place a WHERE clause that eliminates all rows in a SELECT statement, SQL does not return any values. Since the assignment is part of the SELECT clause, it does not happen.

    Consider:

    SELECT @a = (SELECT 1 WHERE 1=2) -- will assign NULL

    SELECT @a = (SELECT 1) WHERE 1=2 -- will not assign a value

    In short, it's not SET vs SELECT, but sets vs scalars and judicious placement of your WHERE clause.

  • Ed Wagner

    SSC Guru

    Points: 286958

  • Simon Jackson

    SSC Rookie

    Points: 39

    (Posted this before seeing the other answer above. Yes you have put it succinctly.)

    Hi,

    Sorry, I was just trying to use the minimum code.

    So with code:

    DECLARE @SomeTable TABLE (x int)

    DECLARE @a int = 0;

    DECLARE @B INT = 0;

    SELECT @a = x FROM @SomeTable -- 1.

    SET @B = (SELECT x FROM @SomeTable) -- 2.

    SELECT @B = (SELECT x FROM @SomeTable) -- 3.

    SELECT @a as a, @B as b

    Lines marked 2 & 3 give identical results. The reason is that when you do the set/select from a subquery, you get one row returned which is null.

    Try:

    select (select x from @SomeTable);vsselect x from @SomeTable;

    In the latter, no rows are returned because the table is obviously empty.

    So the difference is not the use of set/select, it is that the underlying query is returning no rows in the first case but a single null row in the second.

  • webrunner

    One Orange Chip

    Points: 29902

    Great question, thanks!

    - webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and says Can I join you?
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • TomThomson

    SSC Guru

    Points: 104767

    edit: Rubbish deleted.

    I seem to writing nonsense today - must remember to switch brain on before commenting on anything. Or possibly just not try thinking about SQL at same time as listening listening to wife twittering incomprehensible nonsense about something utterly meaningless, since that listening requires brain to be off.

    Tom

Viewing 15 posts - 1 through 15 (of 28 total)

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