SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Intersect, Except, Union, All and Any


Intersect, Except, Union, All and Any

Author
Message
steven.malone
steven.malone
SSC-Enthusiastic
SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)

Group: General Forum Members
Points: 126 Visits: 227
Note: I am an old curmudgeon who speaks bluntly. Nothing that follows is a personal attack. I just see this as a teachable moment.
So:

Let me get this straight:
You stumbled over some new features.
You glanced at books online
You put together some trivial tests against a trivial database
You decide you are now enough of an expert to write an article about it
You write an article that spends more time comparing the performance of trivial tests to six decimal places than into what they actually do.

If you had spent even a few minutes on Google or Wikipedia you would have learned a lot and been in a position to write a useful article.
Instead you went on the assumption that if you could find good uses for the features then there were no good uses for them. I will let you supply the adjective for that attitude.

To everyone who thought it was a great article:
It was a good topic to bring up, but did you read the other comments before you added yours?
David.Poole
David.Poole
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7458 Visits: 3280
Let me get this straight:
You stumbled over some new features.

CORRECT. In the Microsoft course material


You glanced at books online

Did a bit more than glance and also read what little was available in 2009. Not sure when the Wikipedia article originated as it doesn't have an original date, only a modified date in 2011.

I read BOL and the Microsoft course notes plus the standard Googling. What was available looked very like the "Other editions SQL2005" version on BOL.


You put together some trivial tests against a trivial database


CORRECT. I did say it was Adventureworks. The whole point of using Adventureworks is that is a publicly available database. Any tests or experiments run on Adventureworks can be easily repeated and therefore verified by developers and DBAs of all levels of ability.



You decide you are now enough of an expert to write an article about it


No, I was asked by the people being put through the course what these operators were and as I hadn't seen them before I did some digging and basic experimenting.

As with any experiment
1. Document the experiment so it is repeatable and up for critique.
2. Document the results so they are verifiable

I don't have the attitude of expecting people to bask in my magnificence. I'm not that arrogant.
What I want to achieve in any article
1. Pass on what I have learnt to those coming up the ranks being very clear about my methods and experiments
2. Provoke response from the many ahead of me in the ranks so that I learn more.

Actually the forum responses did teach me something

select fieldlist from OriginalSource query Where qualifying criteria is met
intersect
select fieldlist from Targettable

Very useful in data warehouse reconcilliation.

I was arrogant enough to think that I could add some clarity to BOL.



Instead you went on the assumption that if you could find good uses for the features then there were no good uses for them.


I can't recall making that assumption, it certainly doesn't say anything about assumptions in the article?

Again the article and results are up for critique and as per the responses in the forum the true use for these operators was revealled.


I will let you supply the adjective for that attitude.


I don't have that attitude, never have had, never will have. You are very much holding the wrong end of the stick and no, I am not making the assumption that it is a stick in your hand.

Merry Christmas

LinkedIn Profile

Newbie on www.simple-talk.com
Ragesh Chavarattil
Ragesh Chavarattil
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 17
My use of Except, Intersect is to prepare a slowly changing dimension table as written below. This will help me to get the changed address as well as new records. How can i re-write this in sql without EXCEPT operand

SELECT CustomerID, Address1, District, Phone1, Phone2,....some 50 col FROM CustAddress_OLTP
EXCEPT
SELECT CustomerID, Address1, District, Phone1, Phone2,....some 50 col FROM CustAddress_SCD
Tracy McKibben
Tracy McKibben
SSC-Enthusiastic
SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)

Group: General Forum Members
Points: 116 Visits: 798
I've actually used INTERSECT and EXCEPT quite a bit, not as a replacement for inner joins as some of these comments imply, but to simplify the comparison of two complex resultsets.

I've also used INTERSECT as a performance tweak:
http://www.real-sql-guy.com/2011/11/stop-performance-problem-with-intersect.html
jgrattan
jgrattan
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 52
Thanks for taking the time to create this post. I have often asked myself if there was any advantage in using some of these commands.
villersk
villersk
SSC-Enthusiastic
SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)

Group: General Forum Members
Points: 185 Visits: 167
I agree. I have found 'excep't very useful when many columns are involved in a table and I want to exclude one entire table from the other. I think you will find in that case except will outperform a join or a not in statement. At least that is what I found on sql2008 r1
dbursey
dbursey
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 Visits: 184
I really liked this article - very thorough. EXCEPT seems to be an analog to the Oracle MINUS, so sometimes I like it because I feel like I really know what's happening there (Oracle SQL being my first SQL language). But, my alternative is to use NOT EXISTS, so I wonder if you would consider expanding this to include the EXISTS/NOT EXISTS alternatives? I'm curious to see how they stack up within your example stream.

In general, I really liked the approach and illustrations (both pictoral and logical).

Thank you --

Donna
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search