TSQL Basics Part 7: LEFT OUTER JOIN w/ exclusion – Video Explanation

This is part 7 “left outer join with exclusion” of a 19 part series on TSQL Basics. You will first gain an understanding of the differences between each of these types of joins, and when they should be used. Then we’ll explore some common uses for joins, such as replacing a not in clause with join to get the same results with better performance. You will take home the right foundations to get at the data that you want. Make sure to check of the other parts in this series in my JOIN Types playlist.

>>> Try our JOIN Types Course Today!

Transcription:

So next we have the LEFT OUTER JOIN with exclusion. And this is one of my favorite ways to find missing data. And what this is going to do, it’s just like the LEFT OUTER JOIN we did before, but we’re saying where the ID from table two is null. So that’s going to say it’s going to return all the rows from table one that don’t match in table two. So let’s take a look at that LEFT OUTER JOIN with exclusion.

If I highlight just the first part of it without the where clause. That’s our basic LEFT OUTER JOIN that we did before where we get everybody including those who have favorite colors, and those who do not have favorite colors. And if we wanted to just get a list of those people who don’t have a favorite color, we can say where the ID is no, which that means this ID column, and it should only return Johnny and Karen in this case.

There we go. So that’s another way to look at everyone in table one who’s going to be out of luck because they don’t have a favorite shirt color or favorite color when they’re getting t shirts printed. Very handy when you’re doing data validation or trying to fill in blanks to be able to go through and find out who’s not matched.

Also just a reminder, you can download the JOIN types poster at Steve Stedman calm. And this is a poster I created a few years ago that just sort of shows how all the different JOINS that we’re going to be covering through this presentation are done. It’s handy to pronounce and keep on your cube wall or near your workspace.

Our JOIN Types Course Comes With:

• Easy to understand lectures with examples

• Assignments

• Sample code to work with

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!