A New Way to Contrast Data

If you’ve been following along with the previous blog posts, you’ll know I like to show different ways to get the same data. With last week’s post on how to find where two tables differ – found here – you know that this week we’re going to see another way of seeing what is shown below:

This is known as a LEFT EXCLUSIVE JOIN or a LEFT ANTI-JOIN. The reason it’s known as an exclusive JOIN is that it contains none of the data from Table B and only the part of table A that is EXCLUSIVE to table A.

Last week, we used this query to get the data for those in the dbo.Person1 table that has a “Male” gender is not in the dbo.Person2 table with an email that ends in “.com”:

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

Another way to get to this data would be the following:

;WITH Point1 AS (SELECT FirstName, LastName, ID
		FROM dbo.Person1
		WHERE Gender = 'Male'),
      Point2 AS (SELECT FirstName, LastName, ID
		FROM dbo.Person2
		WHERE email LIKE '%.com%')
SELECT p1.FirstName, p1.LastName
FROM Point1 P1 
LEFT JOIN Point2 P2 ON P2.FirstName = P1.FirstName AND P2.LastName = P1.LastName
WHERE P2.ID IS NULL

You’ll notice that this is very similar to the way that we did the comparison queries, but two points that make this very different:

1. It uses a LEFT JOIN – which makes sense because this – as seen above – is a LEFT EXCLUSIVE JOIN.
2. There is some funkiness going on in the WHERE clause.

Point 2 is what makes this the EXCLUSIVE JOIN. We look for a field that shouldn’t be NULL and check if it is NULL!

Also, notice that the field we’re checking for here is the same table that we’re using in the LEFT JOIN clause.

Also, be sure to pay attention to the order that the items are joined on. Notice that I have them all as P2.xx = P1.xx. This is to ensure that you don’t have data LEFT JOINing on some parts of the JOIN clause and RIGHT JOINing on other parts! (Yes, it does happen and you probably won’t be able to tell until you dig deep into the data to verify that you aren’t losing data!)

Now, I leave for you the opposite of the LEFT ANTI-JOIN that is shown above. Yes, that is your assignment – if you choose to accept it – for my next post when I’ll go over what it means and how I would do it.

With that, my friends, I bid you adieu. Until my next post!