An underrated operator: using EXCEPT in MS SQL Server to track changes

Working with a colleague who is a longtime Oracle user, I noticed he used an operator I didn’t know of, to grab hold of the differences in rows between two nearly identical tables: he used a “minus”. I couldn’t imagine MS SQL Server would not have something like it and it didn’t, it came up with the “except” operator which does exactly the same.

The trick is that you use it like you are used to with  the union operator. So you have two select-statements that should return the same columns and join with the union:

select A, B, C
from TableA
union
select A, B, C
from TableB

This gives you all rows from both tables. Now replace the union with except, and you will get any rows that are in one table but not in the other.

I found this extremely useful if you are used to creating tables that save that the state of a table on a certain point in time, for example:

select A, B, C
into TableA_snapshot_20140611
from TableA

Then do all kinds of things with TableA, and then do this:

select A, B, C
from TableA
except
select A, B, C
from TableA_snapshot_20140611

which gives you exactly what has changed in TableA since you made the snapshot. It also works with views and temporary tables. May come in quite handy sometimes when debugging queries!

Advertenties

Over Shiftkey
I am Maarten, owner of and chief software developer for Shiftkey software development from The Netherlands. I will be writing mainly about things I run into when programming in C# or Delphi.

Geef een reactie

Vul je gegevens in of klik op een icoon om in te loggen.

WordPress.com logo

Je reageert onder je WordPress.com account. Log uit / Bijwerken )

Twitter-afbeelding

Je reageert onder je Twitter account. Log uit / Bijwerken )

Facebook foto

Je reageert onder je Facebook account. Log uit / Bijwerken )

Google+ photo

Je reageert onder je Google+ account. Log uit / Bijwerken )

Verbinden met %s

%d bloggers liken dit: