EXCEPT

Question

Post reply

EXCEPT

Lynn Pettis

SSC Guru

Points: 442467
More actions
July 11, 2007 at 2:17 pm

#69847

Has anyone else run into problems using EXCEPT in SQL 2005? I have found it is not always accurate it what it returns, and it acts flaky at times as well.
I have found a way to emulate the EXCEPT process thanks to one of the books I purchased, so it isn't critical, just curious if others have had a similiar issue.

Viewing 8 posts - 1 through 7 (of 7 total)

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

Anders Pedersen SSChampion Points: 11410 More actions · Answer 1

I have not noticed. Any patterns as to what you have noticed is not correct?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

The most obvious error I have seen is actually with two small tables with 5 or 6 columns (don't have the data in front of me) and 19 rows in Table1 and 15 rows in Table2. Using the EXCEPT statment as such:

select * from Table1

except

select * from Table2

returns all rows from Table1. Doing a detailed analysis between tables using SQL to compare column values reveales that only 9 rows should have been returned with the above query. Using a method that allow emulation of the EXCEPT, I get the correct result set.

I have another example with a much larger data set that returns all rows from the first table when done:

select * from Table3

except

select * from Table4

but only 4 rows when done:

select * from Table3

except

select * from Table4 order by uniqkey --where this is the PK on the tables

I have another set of tables that the above query for Table3 and Table4 returns all rows regardless of which query I run, and due to the number of rows, haven't done a detailed analysis, but I have found rows that shouldn't have been returned.

steve smith-401573 SSChampion Points: 10889 More actions · Answer 3

For EXCEPT to work, don't you have to guarantee matching sort orders? Which you essentially demonstrated when you added the order by, right?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

The first example I gave you fails even with an order by in the statement. Like I said, it appears to be a bit flaky and inconsistant. I have used EXCEPT with a subset of the columns in several tables without issue, such as determining what primary key exist in the target file for an import but not in the source file being imported.

steve smith-401573 SSChampion Points: 10889 More actions · Answer 5

Why use the EXCEPT statement at all, with a "Select * ..."? What's the original objective here (other than 'drain the swamp'?)

Anders Pedersen SSChampion Points: 11410 More actions · Answer 6

Come to think of it I have never used it with a select *. Maybe there is a problem with that.

Not sure what you mean by draining the swamp... I have used except to find the differences of the result set when modifying a stored procedure etc. Just to verify either that it changed it the way I wanted, or didn't change it if I am working on optimizing code.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Okay, added the column names, same problem. As for draining the swamp, no. I am seeing how this could be applied to find all new and changed records that need to be imported into a table. It seems that using the EXCEPT would make it easier from a coding perspective as it (EXCEPT) knows how to handle nulls with out having to code something like this for multiple columns: coalesce(colA,'') <> coalesce(colB,'') (or something similiar).