What is the Output?

  • Shiva N

    Hall of Fame

    Points: 3125

    Comments posted to this topic are about the item What is the Output?

    Thanks,
    Shiva N
    Database Consultant

  • Nakul Vachhrajani

    SSChampion

    Points: 10216

    I guess the explanation is quite simple:

    Divide by 0 is not causing a problem in the SELECT clause because of the presence of EXISTS. You can have pretty much anything in the SELECT and the result will be the same because all EXISTS does is evaluate for the existance of a record in the result set. Whether the record exists or not (the FROM and WHERE clauses) does not have any bearing on how it is represented (which is the job of the SELECT).

    The divide by 0 will cause issues if it is in the WHERE clause, because that needs to be evaluated fully. Here's the snippet:

    --Query 1:

    if exists(select 1/0 from @tbl where id=10 AND (1/0)=1)

    select 1

    else

    select 2

    However, here's a twist to the tale. The following will *not* return an error:

    --Query 1:

    if exists(select 1/0 from @tbl where id=10 AND 1=0 AND (1/0)=1)

    select 1

    else

    select 2

    This is because the database engine evaluates the constant expression (1=0), finds it to be FALSE and therefore the logical AND acts as a short circuit operator because of which the part [(1/0)=1] is not evaluated at all.

    Thanks & Regards,
    Nakul Vachhrajani.
    http://nakulvachhrajani.com
    Be courteous. Drive responsibly.

    Follow me on
    Twitter: @sqltwins

  • twin.devil

    SSC-Insane

    Points: 22208

    nicely put, good question. thanks for sharing ...

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    We cannot find a reference, but apparently the divide by zero doesn't cause an issue in the EXISTS() clause.

    The examples in this question are actually quite common. They are very often posted in the ever-returning discussion on EXISTS (SELECT * ...) vs EXISTS (SELECT 1 ...) (or any other constant other than 1 - lately NULL appears to be popular). The myth that SELECT * is expanded into a column list still persists, even though that stopped being true several decades ago. During that discussion, there's always someone who posts the 1/0 example to prove that it doesn't matter what you put in the EXISTS.

    There is no very explicit reference, but I think that this link is valuable: https://technet.microsoft.com/en-us/library/ms188336.aspx. It mentions that EXISTS returns true if the subquery contains any rows. From this, one can conclude that the content of those rows is irrelevant. This is somewhat reinforced by example A.

    Nakul Vachhrajani (6/24/2015)


    However, here's a twist to the tale. The following will *not* return an error:

    --Query 1:

    if exists(select 1/0 from @tbl where id=10 AND 1=0 AND (1/0)=1)

    select 1

    else

    select 2

    This is because the database engine evaluates the constant expression (1=0), finds it to be FALSE and therefore the logical AND acts as a short circuit operator because of which the part [(1/0)=1] is not evaluated at all.

    Correction: that code **MIGHT** not return an error. Or it might. The optimizer determines in what order to evaluate the expressions, so short-circuiting is not guaranteed.

    It is very common to see starting SQL developers get upset when a query that uses "SELECT ..., Something / Divisor, ... FROM ... WHERE Divisor <> 0" result in an error - but that is not a bug (it is a nuisance, though!), the optimizer is free to do the division before applying the filter.


    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/

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Easy one, thanks.

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

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Hugo Kornelis (6/24/2015)


    The examples in this question are actually quite common. They are very often posted in the ever-returning discussion on EXISTS (SELECT * ...) vs EXISTS (SELECT 1 ...) (or any other constant other than 1 - lately NULL appears to be popular). The myth that SELECT * is expanded into a column list still persists, even though that stopped being true several decades ago. During that discussion, there's always someone who posts the 1/0 example to prove that it doesn't matter what you put in the EXISTS.

    Ha, I learned about 1/0 in EXISTS because Gail mentioned this trick in a discussion with indeed the subject about what to put in an EXISTS statement 😀

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

  • This was removed by the editor as SPAM

  • Ed Wagner

    SSC Guru

    Points: 286982

    Great example to illustrate a point. Nice question and follow up discussion. I was always in the SELECT 1 camp myself, but admit that it came from Oracle many years ago. I just learned that it doesn't matter. Thank you.

  • g.britton

    SSChampion

    Points: 13689

    Nakul Vachhrajani (6/24/2015)


    I guess the explanation is quite simple:

    Divide by 0 is not causing a problem in the SELECT clause because of the presence of EXISTS. You can have pretty much anything in the SELECT and the result will be the same because all EXISTS does is evaluate for the existance of a record in the result set. Whether the record exists or not (the FROM and WHERE clauses) does not have any bearing on how it is represented (which is the job of the SELECT).

    The divide by 0 will cause issues if it is in the WHERE clause, because that needs to be evaluated fully. Here's the snippet:

    --Query 1:

    if exists(select 1/0 from @tbl where id=10 AND (1/0)=1)

    select 1

    else

    select 2

    However, here's a twist to the tale. The following will *not* return an error:

    --Query 1:

    if exists(select 1/0 from @tbl where id=10 AND 1=0 AND (1/0)=1)

    select 1

    else

    select 2

    This is because the database engine evaluates the constant expression (1=0), finds it to be FALSE and therefore the logical AND acts as a short circuit operator because of which the part [(1/0)=1] is not evaluated at all.

    Pretty sure you can *not* rely on that short-circuiting. It may work 9 times out of 10 then fail.

    see here:

    Gerald Britton, MCSE-DP, MVPToronto PASS Chapter[/url]

  • sestell1

    SSChampion

    Points: 10230

    Hugo Kornelis (6/24/2015)


    Correction: that code **MIGHT** not return an error. Or it might. The optimizer determines in what order to evaluate the expressions, so short-circuiting is not guaranteed.

    It is very common to see starting SQL developers get upset when a query that uses "SELECT ..., Something / Divisor, ... FROM ... WHERE Divisor <> 0" result in an error - but that is not a bug (it is a nuisance, though!), the optimizer is free to do the division before applying the filter.

    This confused me greatly the first time I ran into it. A fellow developer had written a query similar to your example that was initially worked just fine, but suddenly stopped working as the table grew larger. It was a long time before I understood why.

  • webrunner

    SSC-Dedicated

    Points: 30273

    Cool question, thanks.

    - webrunner

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

  • webrunner

    SSC-Dedicated

    Points: 30273

    Hugo Kornelis (6/24/2015)


    We cannot find a reference, but apparently the divide by zero doesn't cause an issue in the EXISTS() clause.

    The examples in this question are actually quite common. They are very often posted in the ever-returning discussion on EXISTS (SELECT * ...) vs EXISTS (SELECT 1 ...) (or any other constant other than 1 - lately NULL appears to be popular). The myth that SELECT * is expanded into a column list still persists, even though that stopped being true several decades ago. During that discussion, there's always someone who posts the 1/0 example to prove that it doesn't matter what you put in the EXISTS.

    There is no very explicit reference, but I think that this link is valuable: https://technet.microsoft.com/en-us/library/ms188336.aspx. It mentions that EXISTS returns true if the subquery contains any rows. From this, one can conclude that the content of those rows is irrelevant. This is somewhat reinforced by example A.

    Nakul Vachhrajani (6/24/2015)


    However, here's a twist to the tale. The following will *not* return an error:

    --Query 1:

    if exists(select 1/0 from @tbl where id=10 AND 1=0 AND (1/0)=1)

    select 1

    else

    select 2

    This is because the database engine evaluates the constant expression (1=0), finds it to be FALSE and therefore the logical AND acts as a short circuit operator because of which the part [(1/0)=1] is not evaluated at all.

    Correction: that code **MIGHT** not return an error. Or it might. The optimizer determines in what order to evaluate the expressions, so short-circuiting is not guaranteed.

    It is very common to see starting SQL developers get upset when a query that uses "SELECT ..., Something / Divisor, ... FROM ... WHERE Divisor <> 0" result in an error - but that is not a bug (it is a nuisance, though!), the optimizer is free to do the division before applying the filter.

    Thanks, Hugo, for this additional explanation!

    - webrunner

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

  • SQL_Hunt

    SSC-Dedicated

    Points: 33450

    I think its not because of divide by zero issue. The results appear as it matches condition 1 so results 1, condition no#2 does not match condition, so result = 2 (elsE)

    Thanks.

  • SQLRNNR

    SSC Guru

    Points: 281243

    Koen Verbeeck (6/24/2015)


    Hugo Kornelis (6/24/2015)


    The examples in this question are actually quite common. They are very often posted in the ever-returning discussion on EXISTS (SELECT * ...) vs EXISTS (SELECT 1 ...) (or any other constant other than 1 - lately NULL appears to be popular). The myth that SELECT * is expanded into a column list still persists, even though that stopped being true several decades ago. During that discussion, there's always someone who posts the 1/0 example to prove that it doesn't matter what you put in the EXISTS.

    Ha, I learned about 1/0 in EXISTS because Gail mentioned this trick in a discussion with indeed the subject about what to put in an EXISTS statement 😀

    I remember those discussions rather well. Those are the good ol' days. 😉

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • SQLRNNR

    SSC Guru

    Points: 281243

    EZ PZ lemon squeezy. That said, it is a rather good lesson as Hugo laid out for us.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

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

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