Intersect, Except, Union, All and Any

SQLServerCentral is supported by Red Gate Software Ltd.

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: Yesterday @ 9:35 AM
Points: 2,749, Visits: 1,407

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

LinkedIn Profile

Post #755353

Jeff Moden

Posted Saturday, July 18, 2009 5:11 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:26 PM
Points: 32,906, Visits: 26,791

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #755361

WayneS

Posted Sunday, July 19, 2009 9:29 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 10:05 AM
Points: 6,368, Visits: 8,231

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: Today @ 1:26 PM
Points: 32,906, Visits: 26,791

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

--Jeff Moden

Post #755423

Kim Vermeij

Posted Monday, July 20, 2009 12:45 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, May 13, 2013 11:48 PM
Points: 11, Visits: 90

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

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, March 06, 2013 10:52 AM
Points: 8, Visits: 70

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 08, 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

Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 12:43 AM
Points: 582, Visits: 1,601

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: Thursday, April 05, 2012 2:35 PM
Points: 2,007, Visits: 767

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