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!