Post reply

exceptional logic

  • October 11, 2011 at 2:47 am

    #1392726

    vk-kirov - thanks for your excellent explanation - much better than mine 🙂

    Ben

    ^ Thats me!

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

  • October 11, 2011 at 3:03 am

    #1392730

    Please go through this:

    http://msdn.microsoft.com/en-us/library/ms188055.aspx

  • October 11, 2011 at 4:17 am

    #1392742

    Top question!

    Thanks for the explanations too

  • October 11, 2011 at 4:27 am

    #1392748

    Good question, definitely not 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010

    I guess some people will be surprised by except and union having lower precedence than intersect.

    I was surprised myself that union and except had the same precedence as each other. I am surprised each time I rediscover it (must remember it better).

    Tom

  • October 11, 2011 at 4:58 am

    #1392760

    Good question Ben - I'd not come across 'except' before.

  • October 11, 2011 at 5:14 am

    #1392765

    paul s-306273 (10/11/2011)

    Good question Ben - I'd not come across 'except' before.

    thanks, I hadnt either. I discovered it during a brain fart moment - I couldn't workout how to do what I wanted so started typing plain english into ssms (I do that sometimes to gather my thoughts - write the logic in real english then codify it) I noticed the the word 'except' was in blue, so, with eyebrow duly raised I googled.

    Ben

    ^ Thats me!

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

  • October 11, 2011 at 5:33 am

    #1392771

    vk-kirov (10/10/2011)

    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.

    Thank you. That makes sense now. 🙂

    Best regards,

    Best regards,

    Andre Guerreiro Neto

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

  • October 11, 2011 at 5:35 am

    #1392772

    Nice question!

  • October 11, 2011 at 6:25 am

    #1392804

    Great question with something valuable to learn. Even though I got the question correct, I did not know that about the precedence difference between EXCEPT, UNION, and INTERSECT. I must have just blundered my way into the correct answer.

    Thanks,

    Matt

  • October 11, 2011 at 6:30 am

    #1392807

    vk-kirov (10/10/2011)

    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.

    Thanks for the order of operations.

  • October 11, 2011 at 6:42 am

    #1392809

    Thanks for a nice example on how to use intersect and except. I run into situations where I could use this syntax all the time but I forget that these keywords are available. I was also unaware that intersect had a higher order of operations than except. Good stuff!

  • October 11, 2011 at 6:44 am

    #1392813

    nice question, strictly speaking shouldn't it be union all?

    ---------------------------------------------------------------------

  • October 11, 2011 at 6:55 am

    #1392820

    :Wow: i got that

  • October 11, 2011 at 6:56 am

    #1392821

    george sibbald (10/11/2011)

    nice question, strictly speaking shouldn't it be union all?

    thanks.

    as for union all, it makes no odds for the purpose of this example or if you're trying to get rows that dont match between 2 tables. It should have exactly the same output (i think, off the top of my head) but depending on the size of the dataset or the number of columns in a row you may find union all vs union to have some performance implications. I don't know tbh which is faster in which situation

    Ben

    ^ Thats me!

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

  • October 11, 2011 at 7:02 am

    #1392826

    I'm very glad to see that so far no-one has answered '3' 😀

    Ben

    ^ Thats me!

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

Viewing 15 posts - 16 through 30 (of 56 total)

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