Opposite of a Left Anti Join

If you followed along with last weeks’ post (found here), then the title of today’s post hopefully makes sense. If not, then I would highly recommend reading it (or re-reading it) so that we all can be on the same page.

Read it? Great. Let’s get started.

Last week I left you with the following code:

;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

With this code, I asked you to write up the opposite of it. Well, the opposite of a left join is simply a right join. And since a left join looks like the following:

We want the following to show – remember that it has to be an anti-join as well so that we do not get any data where the two tables coincide.

Or, in other words, since we have shown that the following code is a Left Anti-Join:

;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

Then, we want to show those data points where the email is like .com in dbo.Person2, but we do not want them to show as Male in dbo.Person1. As one of my coworkers says, “Easy Peasy Lemon Squeezy”

The way that I would do the JOIN for this query is as follows:

;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 p2.FirstName, p2.LastName
FROM Point2 P2
LEFT JOIN Point1 P1 ON P1.FirstName = P2.FirstName AND P2.LastName = P1.LastName 
WHERE P1.ID IS NULL

Notice what is reversed and – just as importantly – what is not reversed.

• P2 and P1 have now flipped places in the FROM and JOIN clauses.
• The WHERE clause is now looking for where P1.ID is null

The thing to keep in mind is that these two will not show the same data. The second query will only show what is in Point2 and not in Point1. These are two very different queries.

Now, when you’re writing the opposite, most people would expect that you would want to use the RIGHT JOIN – with it being the opposite of the LEFT JOIN and all. This most definitely doesn’t have to be true. For me, it is easier to write a LEFT JOIN because of the way the queries work and it truly makes more sense to me to write this way.

My words to all of you:

• Please try to write both ways.
• Make sure that you’re getting the right data.
• Write the queries the way that makes the most sense to you.

Since you’re the one that has to work with the data, let the second point be the one that guides you, “the right data.” Don’t let whether it is a LEFT or RIGHT JOIN be a guiding factor.

Said another way:

And with that, we’ve come to the end of the series on comparing and contrasting data in several different ways. Next time, we’ll talk about a new subject. Until then my friends!