Ways to Contrast Data

As you have probably determined, with blog posts like these:

• Ways to Compare Data
• Different Way to Compare Data

You know when the word compare is used, sooner or later the opposite is going to be used. Yes, folks – this week we are going to be talking about contrasting data.

SELECT * 
FROM dbo.Person1 
EXCEPT 
SELECT * 
FROM dbo.Person2 
ORDER BY ID

You’ll notice the usage of EXCEPT is truly the same as the INTERSECT operator. You are truly just contrasting what is in one query with what is in the other.

There is one thing you will need to remember with EXCEPT that you don’t have to think about with the INTERSECT operator though.

With the use of EXCEPT, you will need to pay attention to what query you would like to look at and compare the other. For example, if I want to know what’s in the dbo.Person2 table, but not in the dbo.Person1 table – make sure that the query listing dbo.Person2 is listed before the EXCEPT operator.

In this case, you will note that the results are divisible by 3 but not divisible by 2 – exactly as expected.

Now, for both of these queries that I’ve shown, you’ll notice that I included the ORDER BY clause. Is it truly necessary? No. I added that so that we can easily tell that the query gave us the data we expected to see. Since the order of the results set is not guaranteed, we should add the ORDER BY clause in almost all cases to make sure that items are going to be shown in the order we expect.

Also, you should make sure to review the data so that we do not get any data that we don’t expect.

For example, let’s look at this query:

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

If we are expecting results that do not have any emails ending in “.com”, then we did the wrong query. Remember that dbo.Person2 has IDs that are divisible by 3 and dbo.Person1 has IDs that are indivisible by 2.

If you are to add the ID column to both of the queries shown, then you can see that there are several IDs of Males from the dbo.Person1 table that are not in the individuals listed with an email ending in “.com” in the dbo.Person2 table.

And with that, I will leave you until next week, when we will discuss other ways to get this same information. Until then my friends!