Geeks With Blogs
SharePoint & SQL Thoughts

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.

Posted on Friday, April 19, 2013 4:03 PM | Back to top


Comments on this post: Let’s talk some T-SQL

Comments are closed.
Comments have been closed on this topic.
Copyright © Leonard Mwangi | Powered by: GeeksWithBlogs.net