Which is faster/better?

[ss1289]

When comparing two tables to see what the differences are? Using an EXCEPT or a WHERE NOT EXISTS?

I'm comparing tables where each have about 2000+ rows.

Also, I usually only get about 5-10 different rows with the EXCEPT (the EXCEPT is what I'm currently using).

[ksailesh]

Where Not Exists

[ss1289]

Where Not Exists

Can you give an explanation on why?

[mattyblah]

except would be faster and better because that is what it's built for. it is also more legible. after all, what would you rather read:

select ColumnA, ColumnB, ColumnC
from TableA
except
select ColumnA, ColumnB, ColumnC
from TableB

or...

select ColumnA, ColumnB, ColumnC
from TableA a where not exists
(select *
from TableB b where
Coalesce(a.ColumnA, '') = Coalesce(a.ColumnA, '')
and Coalesce(a.ColumnB, '') = Coalesce(b.ColumnB, '')
and Coalesce(a.ColumnC, '') = Coalesce(b.ColumnC, '')
)

Full Outer Join is also another option (with the same limitations as not exists) but if you have except available to you there is no reason why you should not use it. Again, that is what the except operator is built for!