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

LinkedIn Profile
www.simple-talk.com

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

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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

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

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
Author - SQL Server T-SQL Recipes

---

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

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

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

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

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

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

I get no images!!!

No Images either. How Useful!!!

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.

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