So you have two tables that you want to compare to see what’s
common between them and what’s not. Traditionally I have used JOINS to get what
I need but did you know you can compare the two tables results simply by using
the EXCEPT and INTERSECT syntaxes?
Let’s look at these two beauties.
EXCEPT – returns any
distinct values from the left (sounds familiar LEFT JOIN) that are not found on
the right query.
Assuming that everyone has a master database that they can
query, run the following queries to see what this beauty is doing. You’ll see
that its returning the result set from my left query (unfiltered query) that’s filtered
from the right query.
USE MASTER
GO
SELECT *
FROM sys.objects
WHERE type = 'U'
EXCEPT
SELECT *
FROM sys.objects
WHERE type = 'U' AND name LIKE '%spt_%'
How about INTERSECT
– This beauty return distinct results that match between my left and the right query example. Going back
to the master database query the result set only contains data that matches on
both queries.
USE MASTER
GO
SELECT *
FROM sys.objects
WHERE type = 'U'
INTERSECT
SELECT *
FROM sys.objects
WHERE type = 'U' AND name LIKE '%spt_%'
So how is this useful? Think of those dupes, or when you just
want to catch the culprit they make things very easy.
Happy tscripting.