exceptional logic

  • in this case the execution plan is identical between union and union all (SORT (DISTINCT SORT)). Precedence got me on this one 🙂 I'll chalk it up to early morning.

  • I honestly never heard of EXCEPT or INTERSECT before either. Seeing them today for the first time, they work exactly as I'd expect them to. I can definitely see myself employing them in the future when comparing two tables, something I do fairly often. For me, extremely valuable QOTD!

  • Nice Question.

    Great example of how UNION, EXCEPT, and INTERSECT work together.

  • nice question . Thanks a lots

    Thanks
    Vinay Kumar
    -----------------------------------------------------------------
    Keep Learning - Keep Growing !!!

  • wware (10/11/2011)


    I honestly never heard of EXCEPT or INTERSECT before either. Seeing them today for the first time, they work exactly as I'd expect them to. I can definitely see myself employing them in the future when comparing two tables, something I do fairly often. For me, extremely valuable QOTD!

    Thanks 🙂 I'm glad it was helpful.

    I'll make sure I submit more as and when I learn new tricks!

    Ben

    ^ Thats me!

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

  • After coming up with the wrong answer....I wondered if there was some kind of precedence with Union, Except, and Intersect. It seems there is, and knowing would have made all the difference.

    Good question, thanks.

  • tks - good question.

  • Forced me to re-read INTERSECT -- thanks!

  • Very Nice Question.

    And Yes, one should always understand the precedence rules. Using parentheses is good way to prevent any sort of possible risks.

    Best Regards,

    Sudhir

  • Sudhir Dwivedi (10/11/2011)


    Very Nice Question.

    And Yes, one should always understand the precedence rules. Using parentheses is good way to prevent any sort of possible risks.

    Best Regards,

    Sudhir

    Absolutely. I would normally include parenthesis or other visual aids in such a query in a production environment, however as you have alluded to, doing so here would have left the most valuable lesson of the question un-taught. That of intersect having precedence over union and except. This is intuitive from a human standpoint but not from a procedural programming standpoint so leaving out the parens forces the reader to consider an implicit order of execution rather than ignoring an explicit one.

    In fact to place parens in this question could even imply to the reader that intersect does NOT have precedence and that the parens are required for the desired order of execution in this case.

    Hence my decision to leave them out of this question 🙂

    In production I might not use parenthesis depending on the context - I would definitely use at least line spacing or comment lines like the below;

    -----

    select * from @foo

    union

    select * from @foo2

    -----

    except

    -----

    select * from @foo

    intersect

    select * from @foo2

    -----

    but it would depend on who was going to maintain my code as to whether or not I included parens, again for reasons of making implications about a function's behaviour.

    Thanks for all your positive comments everyone 🙂

    Ben

    ^ Thats me!

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

  • Outstanding question -- not only on Except/Intersect, but also on operator precedence. A great brain stretcher to get the day started.

    Rob Schripsema
    Propack, Inc.

  • 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 this ^ excellent explanation. Great question!

  • BenWard (10/11/2011)


    wware (10/11/2011)


    I honestly never heard of EXCEPT or INTERSECT before either. Seeing them today for the first time, they work exactly as I'd expect them to. I can definitely see myself employing them in the future when comparing two tables, something I do fairly often. For me, extremely valuable QOTD!

    Thanks 🙂 I'm glad it was helpful.

    I'll make sure I submit more as and when I learn new tricks!

    Used it already, believe it or not!

  • Rob Schripsema (10/11/2011)


    Outstanding question -- not only on Except/Intersect, but also on operator precedence....

    Agreed! Like some of you, I didn't know INTERSECT had higher precedence than UNION and EXCEPT.

  • I also had never heard of EXCEPT and INTERSECT and learned a lot from this QOD. I even had an opportunity to employ INTERSECT in a QA query this afternoon. Very interesting.

    While I find the keywords fascinating, it occurred to me that all this really is doing is an outer join. So I tested it and came up with the same answer using FULL OUTER JOIN. What is interesting is that the execution plan looks slightly less effective using the EXCEPT/INTERSECT keywords. Possibly even more important (for me) is code readability. Perhaps that is just because I am so accustomed to the JOIN syntax.

    At any rate, a great QOD. I learned several things from this one and will look for ways to put these keywords to work for me. Thanks!

    Here is what I used to test my outer join theory:

    ----

    declare @fee as table (fee int)

    declare @fee2 as table (fee int)

    insert into @fee select 1 union select 2 union select 5

    insert into @fee2 select 1 union select 2 union select 4

    select coalesce(a.fee,b.fee) from @fee a

    FULL outer join @fee2 b on a.fee = b.fee

    where b.fee is null or a.fee is null

    ----

    Chris Umbaugh
    Data Warehouse / Business Intelligence Consultant
    twitter @ToledoSQL

Viewing 15 posts - 31 through 45 (of 56 total)

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