SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Stephen Tirone / Caution with EXCEPT / Latest Posts

RE: Caution with EXCEPT

Ion Freeman — Fri, 04 Nov 2011 11:51:32 GMT

Yes, Except seems like the hardest of the join operators for the query engine. Wouldn't the two-way compare[code="sql"]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[/code]Work better as [code="sql"]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[/code]

RE: Caution with EXCEPT

san_kan1gb — Sun, 02 Jan 2011 00:47:58 GMT

Just an info, not to add a column of type timestamp, because it will always be different.Thanx on the good article.

RE: Caution with EXCEPT

CyclingRabbit — Wed, 10 Feb 2010 01:27:57 GMT

Great article. Thanks!

RE: Caution with EXCEPT

SQLista — Wed, 20 Jan 2010 13:00:28 GMT

I think it's worth mentioning that there's also an [b]INTERSECT[/b] 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!

RE: Caution with EXCEPT

Jeff Moden — Tue, 19 Jan 2010 18:07:01 GMT

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

RE: Caution with EXCEPT

jdurandt — Tue, 19 Jan 2010 10:59:45 GMT

Hi JeffI 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 nullIt was faster than both on my test system, using a dbcc freeproccache, dbcc dropcleanbuffers and checking the total cost of the queries.

RE: Caution with EXCEPT

SQLRNNR — Mon, 18 Jan 2010 23:52:56 GMT

Thank you for this article. It is a nice demonstration of another reason why one should avoid the use of[code="sql"]Select *[/code].

RE: Caution with EXCEPT

Clive Chinery — Mon, 18 Jan 2010 23:00:32 GMT

Thank you for the article.

RE: Caution with EXCEPT

susan.falcon — Mon, 18 Jan 2010 17:33:30 GMT

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.

RE: Caution with EXCEPT

JasonRowland — Mon, 18 Jan 2010 15:55:26 GMT

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

RE: Caution with EXCEPT

shannonjk — Mon, 18 Jan 2010 14:20:08 GMT

Stephen, Again a great article, but if I could throw in one little tiny technicality to this I would feel much better :-DWhile 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 :-D

RE: Caution with EXCEPT

gchornenkyy — Mon, 18 Jan 2010 12:56:50 GMT

For bugmenot-573553:Try do develop correct indexes - it may help

RE: Caution with EXCEPT

shannonjk — Mon, 18 Jan 2010 12:48:28 GMT

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.

RE: Caution with EXCEPT

Joseph M. Steinbrunner — Mon, 18 Jan 2010 12:25:39 GMT

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.

RE: Caution with EXCEPT

ChrisM@Work — Mon, 18 Jan 2010 10:17:33 GMT

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!

RE: Caution with EXCEPT

Jeff Moden — Mon, 18 Jan 2010 09:56:32 GMT

Nicely done, Stephen. Well written and straight to the point.

RE: Caution with EXCEPT

Jeff Moden — Mon, 18 Jan 2010 09:53:55 GMT

[quote][b]jdurandt (1/18/2010)[/b][hr]That's interestingI benchmarked EXCEPT vs WHERE NOT IN (SELECT .....), and for my dataset, server, DB version etc. the performance results were equivalent, or biased towards EXCEPT.Can you give more details about your scenario?[/quote]Can you post your benchmark code, please?

RE: Caution with EXCEPT

Mike DiRenzo — Mon, 18 Jan 2010 08:34:24 GMT

Bravo! What a great catch. You have my 5 star vote.-Mike

RE: Caution with EXCEPT

jdurandt — Mon, 18 Jan 2010 08:22:14 GMT

I find that an easier way to get the list of columns is to drag them from the object explorer in SSMS. If you expand the table object's columns list, you can then drag the "Columns" parent entry into a query window, to get the comma seperated list of columns.This works for some other items too.

RE: Caution with EXCEPT

jdurandt — Mon, 18 Jan 2010 08:20:20 GMT

That's interestingI benchmarked EXCEPT vs WHERE NOT IN (SELECT .....), and for my dataset, server, DB version etc. the performance results were equivalent, or biased towards EXCEPT.Can you give more details about your scenario?

RE: Caution with EXCEPT

Sam Jumper — Mon, 18 Jan 2010 08:05:18 GMT

Thanks for the article, I've never used EXCEPT because I didn't realize it existed. On the point of poor performance with large datasets, how can we expect anything else? Probably too many columns to put in a covering index. SQL has to compare every column to every column, it's going to either table scan or bookmark lookup every row. Give your SQL Server a break! I suppose when I think about it, you could include a WHERE statement to break your large datasets in to smaller ones if appropriate to what you are looking for.

RE: Caution with EXCEPT

bugmenot-573553 — Mon, 18 Jan 2010 07:39:31 GMT

We have seen poor performance with EXCEPT on large data sets.

RE: Caution with EXCEPT

ChrisM@Work — Mon, 18 Jan 2010 07:14:34 GMT

[quote][b]Tobar (1/18/2010)[/b][hr]The programmer "intended" it to work the way the programmer thought they were programming it to work. :-D [this said in the universal sense][/quote]LOL you used to work there too!

RE: Caution with EXCEPT

Tobar — Mon, 18 Jan 2010 07:11:12 GMT

The programmer "intended" it to work the way the programmer thought they were programming it to work. :-D [this said in the universal sense]

RE: Caution with EXCEPT

ChrisM@Work — Mon, 18 Jan 2010 06:12:17 GMT

[quote][b]Stephen Hirsch (1/18/2010)[/b][hr]Here's a better way:SELECT 'IN A NOT B' location, a, b, c, d, ...FROM tableaEXCEPTSELECT 'IN A NOT B' location, a, b, c, d, ...FROM tablebUNION ALLSELECT 'IN B NOT A' location, a, b, c, d, ...FROM tablebEXCEPTSELECT 'IN B NOT A' location, a, b, c, d, ...FROM tableaNo parentheses this way.[/quote]Wait a minute! If ever there was a case [i]for[/i] parentheses, it's right here - what was the intent of the programmer? Parentheses make the intent self-documenting:SELECT 'IN A NOT B' location, a, b, c, d, ...FROM tableaEXCEPT(SELECT 'IN A NOT B' location, a, b, c, d, ...FROM tablebUNION ALLSELECT 'IN B NOT A' location, a, b, c, d, ...FROM tablebEXCEPTSELECT 'IN B NOT A' location, a, b, c, d, ...FROM tablea)CheersChrisM

RE: Caution with EXCEPT

Stephen Hirsch — Mon, 18 Jan 2010 05:25:10 GMT

Here's a better way:SELECT 'IN A NOT B' location, a, b, c, d, ...FROM tableaEXCEPTSELECT 'IN A NOT B' location, a, b, c, d, ...FROM tablebUNION ALLSELECT 'IN B NOT A' location, a, b, c, d, ...FROM tablebEXCEPTSELECT 'IN B NOT A' location, a, b, c, d, ...FROM tableaNo parentheses this way.

RE: Caution with EXCEPT

Tobar — Mon, 18 Jan 2010 04:57:09 GMT

[quote][b]mohd.nizamuddin (1/17/2010)[/b][hr]Very nice and informative article...This is what we do in real life as well (Use of SELECT *). Even though we hear from everybody that to avoid SELECT * always.So, if we make practice of writing SELECT col1, col2, col3,.... then we would not get that problem.Enjoy.[/quote]Sorry for 2 responses. More thoughts occurred as the coffee kicked in.I "always" ;-) start to worry when someone used the phrase "always", there are always reasons why always is never appropriate. (Same goes with "never"). Sometimes it is just the thing to do.When needing to "select col1, col2, etc." I use the Management Studio tactic of "Script table as->Select into->new query window" and then just hack out any columns I do not want. It "Always" works for me.

RE: Caution with EXCEPT

Tobar — Mon, 18 Jan 2010 04:48:57 GMT

Another issue with select * is that if you have identity keys and the tables are independently generated (as in deve and staging) and an identical data entry has separate "identities" on the different boxes select * will always return all the rows.Instead of the Union I usually justselect * from Aexceptselect * from Bselect * from Bexceptselect * from Athen you can it is easier to know where the "differences" originate.

RE: Caution with EXCEPT

mohd.nizamuddin — Sun, 17 Jan 2010 23:58:06 GMT

Very nice and informative article...This is what we do in real life as well (Use of SELECT *). Even though we hear from everybody that to avoid SELECT * always.So, if we make practice of writing SELECT col1, col2, col3,.... then we would not get that problem.Enjoy.

Caution with EXCEPT

zintp — Sat, 16 Jan 2010 18:28:14 GMT

Comments posted to this topic are about the item [B]Caution with EXCEPT[/B]