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


Caution with EXCEPT


Caution with EXCEPT

Author
Message
JasonRowland
JasonRowland
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 Visits: 1152
Nice article. Thank you for taking the time to do this.
susan.falcon
susan.falcon
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 25
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
Clive Chinery
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1269 Visits: 154
Thank you for the article.
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33288 Visits: 18560
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

jdurandt
jdurandt
SSC Journeyman
SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)

Group: General Forum Members
Points: 92 Visits: 149
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
Jeff Moden
SSC Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89165 Visits: 41143
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. :-P

--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
SQLista
SQLista
SSC Veteran
SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)

Group: General Forum Members
Points: 275 Visits: 84
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
CyclingRabbit
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 217
Great article. Thanks!
san_kan1gb
san_kan1gb
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 33
Just an info,
not to add a column of type timestamp, because it will always be different.

Thanx on the good article.
Ion Freeman
Ion Freeman
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 123
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


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