Ways to Compare Data

Today’s one of my favorite days – the day we start a new series of posts to help people out with their data!

This week, I want to talk about one of the many things that people have asked me to do for them because they think it’s just too hard.

This week, I want to talk about comparisons between the two tables.

First, we obviously have to have 2 different tables with similar-ish data. Since we just finished a series on people, let’s use some of that data. I’ll create 2 different tables from that one so that can do all kinds of tricks with that data.

If you didn’t create get the data from the last series, you can always get it here. From there, we will run the following commands to create the 2 tables that we’ll be working with in this series.

SELECT *
INTO dbo.Person1
FROM dbo.MockPersonData
WHERE CONVERT(int, ID) % 2 = 0

SELECT *
INTO dbo.Person2
FROM dbo.MockPersonData
WHERE CONVERT(int, ID) % 3 = 0

This will place those persons with an ID divisible by 2 into dbo.Person1 and those persons with an ID divisible by 3 into dbo.Person2.

To find where those two tables have the exact same values, we will use the INTERSECT operator of SQL.

SELECT *
FROM dbo.Person1
INTERSECT 
SELECT *
FROM dbo.Person2

In this case, since all of the fields are the same for both tables, we can use the * to signify that we want all fields. Since most of the time that we’re comparing, we will have vastly different fields and/or field names, we should list them out.

Also, worth noting, is the fact that you don’t have to compare all of the data from two tables against each other. You can compare any two sets of data within the tables. For example, look at the following comparison:

SELECT FirstName, LastName
FROM dbo.Person1
WHERE Gender = 'Male'
INTERSECT 
SELECT FirstName, LastName
FROM dbo.Person2
WHERE email LIKE '%.com'

In this example, we’re comparing the first and last names of all of those persons who identify as Male with all of those persons who have an email that ends with “.com.” (I get 13 results from this INTERSECT query.)

For all of you playing along at home, the INTERSECT operator has been available since SQL Server 2000, so chances are pretty good that you will have the ability to use the INTERSECT operator.

One thing of note about the INTERSECT operator, if you have the same data listed multiple times in both tables, you will only get the data listed once in the query where you use the INTERSECT operator.

With that, we will place a hold on this series until next time where I will discuss another way you might have been getting this data over the years, but it was a lot of work at times. So until then!