October 11, 2011 at 11:53 am
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
October 11, 2011 at 3:17 pm
Chris Umbaugh (10/11/2011)
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
----
Joins will give you information horizontally and the union, intercept, and except will give you information vertically.
October 12, 2011 at 1:43 am
Enjoyed that one.
Just a little surprised that the explanation seems a little brief and misses out the key point of precedence.
Whilst I wasn't sure what the precedence was, anybody who knew what UNION, EXCEPT and INTERSECT do should have been able to figure out that if they chose the incorrect precedence, there would have been no options available for their answer.
Perhaps a little cheaty? But implied the correct precedence quite nicely for me.
October 12, 2011 at 1:48 am
Chris Umbaugh (10/11/2011)
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
----
difference being that if your source tables have 97 columns in each you wouldnt really want to have to write on a.col1 = b.col1 and a.col2 ... ad nauseum 🙂
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
October 12, 2011 at 6:47 am
Ben,
I absolutely will be using these keywords now that I know about them and what they do. I have a habit of comparing and contrasting "new-to-me" techniques and tools with those that have proven themselves in my work. It helps me to attain a more complete understanding of the topic that I am learning so that I can make a more informed decision about which tool will best help me accomplish a given task.
I agree that there may be some tipping point where the number of columns and the method I put forth would make a person want to scream. That said, I also have an aversion to using 'select *' in productionized code so there may be some decent amount of work ahead of me regardless.
Thanks again for the education!
Chris Umbaugh
Data Warehouse / Business Intelligence Consultant
twitter @ToledoSQL
October 12, 2011 at 1:04 pm
Thanks for that question.
A very simple but effective piece of SQL that will useful.
Regards
October 13, 2011 at 5:17 am
good question!
October 19, 2011 at 6:52 am
Good question.Learned one new thing regarding precedence among UNION,INTERSECT,EXCEPT
Malleswarareddy
I.T.Analyst
MCITP(70-451)
October 21, 2011 at 1:36 am
if trying to find different/missing rows across 2 tables (writers suggested use of INTERSECT), I have always used OUTER JOIN (or FULLJOIN) constructs [on all SQL versions], such as
declare @Foo as table (foo int)
declare @foo2 as table (foo int)
select Afoo=A.foo, Bfoo=B.foo -- #1 A and B (equijoin)
from @Foo A
join @foo2 B on B.foo=A.foo
select Afoo=A.foo, Bfoo=B.foo -- #2 A maybe B
from @Foo A
left join @foo2 B on B.foo=A.foo
select Afoo=A.foo, Bfoo=B.foo -- #3 B maybe A
from @Foo A
right join @foo2 B on B.foo=A.foo
select Afoo=A.foo, Bfoo=B.foo -- #4 A or B
from @Foo A
full join @foo2 B on B.foo=A.foo
select Afoo=A.foo, Bfoo=B.foo -- #5 A or B
from @Foo A
cross join @foo2 B
but devs should understand when to use the ON and WHERE clauses correctly
- probably fruitful of another question-of-day !
Dick
October 21, 2011 at 5:52 am
dick.baker (10/21/2011)
if trying to find different/missing rows across 2 tables (writers suggested use of INTERSECT), I have always used OUTER JOIN (or FULLJOIN) constructs [on all SQL versions], such as<snip>
but devs should understand when to use the ON and WHERE clauses correctly
- probably fruitful of another question-of-day !
Dick
As someone else pointed out, that's all very well until you have a lot of columns instead of just one, and then it becomes a large chunk of code which is a pain to maintain when tables are altered (columns added or removed), unless you write a generator for it (working from sys.columns) and use exec to execute it - which can cause other pain.
Tom
October 25, 2011 at 11:36 pm
Very interesting question, but some more explanation on the precedence would have been nice.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 4, 2013 at 5:05 am
MarkusB (10/11/2011)
Very good question even though I missed the point that INTERSECT precedes EXCEPT
now its a complete Ans ...
Neeraj Prasad Sharma
Sql Server Tutorials
Viewing 12 posts - 46 through 56 (of 56 total)
You must be logged in to reply to this topic. Login to reply