Caution with EXCEPT

Question

Caution with EXCEPT

Viewing 15 posts - 16 through 29 (of 29 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Some articles are well worth reading twice.

I've spent all afternoon working for the first time with EXCEPT instead of the usual LEFT JOIN and checking for NULL, and it's taken half the keystrokes with no noticeable performance cost. Thanks!

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Joseph M. Steinbrunner Mr or Mrs. 500 Points: 583 More actions · Answer 2

Good explanation. I saw what was coming on your first SQL example right away (as some others did) when the use of "SELECT *" was there.

shannonjk SSCarpal Tunnel Points: 4286 More actions · Answer 3

Good article Stephen I am going to write the points down of this article in some notes I am gathering.

I wish that they would design the inverse of the INTERSECT operator. As it will show you all the rows matching from both sides of the statement. If they had a NOT INTERSECT it seems like it would return the results of all the aforementioned UNION queries.

Link to my blog http://notyelf.com/

gchornenkyy Say Hey Kid Points: 687 More actions · Answer 4

For bugmenot-573553:

Try do develop correct indexes - it may help

shannonjk SSCarpal Tunnel Points: 4286 More actions · Answer 5

Stephen,

Again a great article, but if I could throw in one little tiny technicality to this I would feel much better 😀

While I realize the emphasis of your article was against 2 tables, and therefore you wrote it as such, I would like to point out that the left and right side of EXCEPT, INTERSECT, and UNION Operators is the comparison of 'query results' and not 'tables'. Though a seemingly minor point, I felt necessary to point it out 😀

Link to my blog http://notyelf.com/

JasonRowland SSC Eights! Points: 951 More actions · Answer 6

Nice article. Thank you for taking the time to do this.

susan.falcon SSC Rookie Points: 34 More actions · Answer 7

Good article. What many people may not realize is that EXCEPT is a SET OPERATOR. The other SET OPERATORS available in MS SQL Server are UNION (ALL) and INTERSECT.

The purpose for using a set operator is to combine the results of multiple select statements into a single result set.

UNION returns all distinct rows from all statements.

UNION ALL returns all rows, including duplicates from all statements.

INTERSECT returns only rows that exist in all statements.

As you pointed out, any time you use a set operator, the number and order of the columns must be the same in all queries and the data types must be compatible.

Clive Chinery SSCrazy Points: 2563 More actions · Answer 8

Clive Chinery

SSCrazy

Points: 2563

January 18, 2010 at 11:00 pm

#1105238

Thank you for the article.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

Thank you for this article. It is a nice demonstration of another reason why one should avoid the use of

Select *.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

jdurandt SSChasing Mays Points: 628 More actions · Answer 10

Hi Jeff

I do not have the code handy at present, and am too sick right now to recreate it 🙂

But in my case I was filtering rows out based on a primary key, so I was using except instead of:

- Select ... from tab1 where tab1.key not in (select key from tab2)

- Select ... from tab1 left outer join tab2 on tab1.key = tab2.key where tab2.key is null

It was faster than both on my test system, using a dbcc freeproccache, dbcc dropcleanbuffers and checking the total cost of the queries.

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 11

Thanks for the feedback and I hope you get well soon.

As a side bar, if "total cost" is coming from the execution plan, then possibly a bad test. I can show you code wth two queries of 0% and 100% yet when they run, exactly the opposite is true even though the actual execution plan says it didn't happen that way. 😛

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLista Old Hand Points: 393 More actions · Answer 12

I think it's worth mentioning that there's also an INTERSECT function for finding the overlapping rows. I spend many, many hours validating data and even I don't use intersect nearly as much as except, it can still be extremely useful.

Great article highlighting an under-used function!

CyclingRabbit Say Hey Kid Points: 693 More actions · Answer 13

CyclingRabbit

Say Hey Kid

Points: 693

February 10, 2010 at 1:27 am

#1116614

Great article. Thanks!

san_kan1gb Grasshopper Points: 24 More actions · Answer 14

Just an info,

not to add a column of type timestamp, because it will always be different.

Thanx on the good article.

Ion Freeman Right there with Babe Points: 731 More actions · Answer 15

Yes, Except seems like the hardest of the join operators for the query engine. Wouldn't the two-way compare

SELECT * FROM

(

SELECT * FROM Staging.dbo.WIDGET

EXCEPT

SELECT * FROM Production.dbo.WIDGET

) LEFT_DIFFS

UNION

SELECT * FROM

(

SELECT * FROM Production.dbo.WIDGET

EXCEPT

SELECT * FROM Staging.dbo.WIDGET

) RIGHT_DIFFS

Work better as

SELECT * FROM

(

SELECT * FROM Staging.dbo.WIDGET

UNION ALL

SELECT * FROM Production.dbo.WIDGET

) BOTH_SETS

EXCEPT

SELECT * FROM

(

SELECT * FROM Production.dbo.WIDGET

INTERSECTION

SELECT * FROM Staging.dbo.WIDGET

) INTSCT