Intersect, Except, Union, All and Any

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by David Poole » Intersect, Except, Union, All and Any

87 posts, Page 1 of 9

1 2 3 4 5 »»»

Intersect, Except, Union, All and Any

Rate Topic

Display Mode

Topic Options

Author

Message

David.Poole

Posted Saturday, July 18, 2009 3:57 PM

SSCrazy

Group: General Forum Members
Last Login: Thursday, February 26, 2015 6:49 AM
Points: 2,943, Visits: 1,922

Comments posted to this topic are about the item Intersect, Except, Union, All and Any

LinkedIn Profile
Newbie on www.simple-talk.com

Post #755353

Jeff Moden

Posted Saturday, July 18, 2009 5:11 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:07 PM
Points: 36,579, Visits: 33,333

I had similar feelings when PIVOT came out. Proves that "new" or "different" is frequently not better, faster, or easier to read. Tongue

Good article David. Thanks for taking the time to write it and to do the comparisons.

--Jeff Moden

"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." Wink

--22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems

Post #755361

WayneS

Posted Sunday, July 19, 2009 9:29 AM

SSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 1:16 PM
Points: 5,387, Visits: 9,121

Very good article David.

One area where I find INTERSECT / EXCEPT easier to work with is when dealing with multiple columns, such as what you might find for a PK. It would really be interesting to see a comparison between the different methods using multiple columns instead of just a single column.

Wayne
Microsoft Certified Master: SQL Server 2008

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

Post #755394

Jeff Moden

Posted Sunday, July 19, 2009 3:51 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:07 PM
Points: 36,579, Visits: 33,333

Why would anyone think that INTERSECT is easier than INNER JOIN?

--Jeff Moden

--22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems

Post #755423

Kim Vermeij

Posted Monday, July 20, 2009 12:45 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, May 16, 2014 3:30 AM
Points: 14, Visits: 119

Hey David,

Nice article, I think you'll find the advantage with EXCEPT when you have to compare multiple columns with possible null values on both sides. When you compare null values with a JOIN or a WHERE statement you will get a "wrong" result, when using the EXCEPT statement you will get the "right" result.

see also:http://en.wikipedia.org/wiki/Null_(SQL)#Grouping_and_sorting

Kim

Post #755534

richard-674310

Posted Monday, July 20, 2009 2:39 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, June 2, 2014 3:30 PM
Points: 11, Visits: 80

When I look at the article none of the images show up? I see no other comments in the forum - is it just me? For ex...

gives me nothing

Post #755591

andrew.amess

Posted Monday, July 20, 2009 2:59 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, August 12, 2009 10:29 AM
Points: 1, Visits: 15

I get no images!!!

Post #755604

conradr

Posted Monday, July 20, 2009 3:26 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, December 8, 2009 8:23 AM
Points: 11, Visits: 24

No Images either. How Useful!!!

Post #755620

David McKinney

Posted Monday, July 20, 2009 4:17 AM

Say Hey Kid

Group: General Forum Members
Last Login: Friday, February 13, 2015 2:24 AM
Points: 661, Visits: 1,923

Nice article!

I've used EXCEPT a few times recently - simply cos I could, I guess, and also sometimes (even if it's relatively unknown syntax) it is clearer when reading the code what is going on.

One limiting aspect of EXCEPT (and also INTERSECT - although I haven't used it) is that the columns have to match (like they do for a UNION) whereas with NOT IN / NOT EXISTS etc. there is no such restriction.

Post #755635

Noel McKinney

Posted Monday, July 20, 2009 6:14 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, February 11, 2014 4:12 PM
Points: 2,007, Visits: 768

Same here, no images, just the red x in a box.

Post #755682

« Prev Topic | Next Topic »

87 posts, Page 1 of 9

1 2 3 4 5 »»»

Permissions