October 11, 2011 at 7:13 am
BenWard (10/11/2011)
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
for the case given the results are the same.
If you know the results will be the same union all should be faster as it does not need to check if duplicates should be removed.
---------------------------------------------------------------------
October 11, 2011 at 7:23 am
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.
October 11, 2011 at 7:26 am
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!
October 11, 2011 at 7:35 am
Nice Question.
Great example of how UNION, EXCEPT, and INTERSECT work together.
October 11, 2011 at 7:44 am
nice question . Thanks a lots
Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
October 11, 2011 at 7:55 am
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
----------------------------------------
October 11, 2011 at 8:23 am
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.
October 11, 2011 at 8:52 am
tks - good question.
October 11, 2011 at 8:56 am
Forced me to re-read INTERSECT -- thanks!
October 11, 2011 at 9:02 am
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
October 11, 2011 at 9:34 am
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
----------------------------------------
October 11, 2011 at 9:44 am
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.
October 11, 2011 at 10:15 am
vk-kirov (10/10/2011)
Nice questioncodebyo (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!
October 11, 2011 at 10:28 am
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!
October 11, 2011 at 11:45 am
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.
Viewing 15 posts - 31 through 45 (of 56 total)
You must be logged in to reply to this topic. Login to reply