exceptional logic

  • BenWard

    SSCertifiable

    Points: 5903

    Comments posted to this topic are about the item exceptional logic

    Ben

    ^ Thats me!

    ----------------------------------------
    01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
    ----------------------------------------

  • Ron McCullough

    SSC Guru

    Points: 63877

    Nice question

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Andre Guerreiro

    SSCertifiable

    Points: 7319

    That looks and executes right... but I can't understand why the result is not only the number 4.

    After the EXCEPT, only the number 4 remains. So where does that 5 come from in the INTERSECT part?

    I'm testing it in SSMS but I don't get it. :unsure:

    Best regards,

    Best regards,

    Andre Guerreiro Neto

    Database Analyst
    http://www.softplan.com.br
    MCITPx1/MCTSx2/MCSE/MCSA

  • SQLRNNR

    SSC Guru

    Points: 281210

    Thanks

    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

  • vk-kirov

    SSCertifiable

    Points: 7686

    Nice question

    codebyo (10/10/2011)


    That looks and executes right... but I can't understand why the result is not only the number 4.

    After the EXCEPT, only the number 4 remains. So where does that 5 come from in the INTERSECT part?

    Please be aware that the INTERSECT operand has higher precedence than the EXCEPT and UNION operands (http://msdn.microsoft.com/en-us/library/ms188055.aspx):

    EXCEPT and INTERSECT (Transact-SQL)

    Remarks

    If EXCEPT or INTERSECT is used together with other operators in an expression, it is evaluated in the context of the following precedence:

    1. Expressions in parentheses

    2. The INTERSECT operand

    3. EXCEPT and UNION evaluated from left to right based on their position in the expression

    So, the code given in the QOTD is equal to the following pseudocode:

    ( (1, 2, 5)

    union

    (1, 2, 4) )

    except

    ( (1, 2, 5)

    intersect

    (1, 2, 4) )

    Which is equal to the following pseudocode:

    (1, 2, 4, 5)

    except

    (1, 2)

    Which gives us "4, 5" as a result.

  • Nakul Vachhrajani

    SSChampion

    Points: 10156

    This is a good, useful QotD. Thank-you!

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

    Follow me on
    Twitter: @sqltwins

  • palotaiarpad

    SSCarpal Tunnel

    Points: 4889

    That was a nice question. But in production code i would have used paraenthesis for better readability. 😛

  • Carlo Romagnano

    SSC-Insane

    Points: 21790

    palotaiarpad (10/11/2011)


    That was a nice question. But in production code i would have used paraenthesis for better readability. 😛

    Me, too! 😀

  • MarkusB

    SSC-Dedicated

    Points: 37369

    Very good question even though I missed the point that INTERSECT precedes EXCEPT

    [font="Verdana"]Markus Bohse[/font]

  • This was removed by the editor as SPAM

  • Stuart Davies

    SSCoach

    Points: 18874

    Good question - thanks.

    -------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a questionThere are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan I would never join a club that would allow me as a member - Groucho Marx

  • BenWard

    SSCertifiable

    Points: 5903

    Carlo Romagnano (10/11/2011)


    palotaiarpad (10/11/2011)


    That was a nice question. But in production code i would have used paraenthesis for better readability. 😛

    Me, too! 😀

    LOL that would mean that my colleagues could read my code! Can't be having that now can we 🙂

    Ben

    ^ Thats me!

    ----------------------------------------
    01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
    ----------------------------------------

  • sqlnaive

    SSCoach

    Points: 17435

    Good question.

    Thanks vk-kirov for the explanation. However still would like to know the sequential execution of the last query.

  • call.copse

    SSCoach

    Points: 16855

    A very nice question - this is why I continue to do the QOTD. I had honestly never heard of INTERSECT or EXCEPT until today - unbelievable! The precedence totally made sense to me although obviously you would want brackets in real life.

  • cengland0

    SSCertifiable

    Points: 6102

    vk-kirov (10/10/2011)

    Please be aware that the INTERSECT operand has higher precedence than the EXCEPT and UNION operands (http://msdn.microsoft.com/en-us/library/ms188055.aspx):

    EXCEPT and INTERSECT (Transact-SQL)

    Remarks

    If EXCEPT or INTERSECT is used together with other operators in an expression, it is evaluated in the context of the following precedence:

    1. Expressions in parentheses

    2. The INTERSECT operand

    3. EXCEPT and UNION evaluated from left to right based on their position in the expression

    So, the code given in the QOTD is equal to the following pseudocode:

    ( (1, 2, 5)

    union

    (1, 2, 4) )

    except

    ( (1, 2, 5)

    intersect

    (1, 2, 4) )

    Which is equal to the following pseudocode:

    (1, 2, 4, 5)

    except

    (1, 2)

    Which gives us "4, 5" as a result.

    Ah, thank you. I too selected just 4 because I didn't know there was a precedence in them and that was not described in the explanation. Makes sense how it works now. Thanks.

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

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