TSQL Basics Part 14: FULL OUTER JOIN w/ exclusion – Video Explanation

This is part 14 “full 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:

Now what we can do is say do a full OUTER JOIN with exclusion. And that’s like the LEFT OUTER JOIN with exclusion or RIGHT OUTER JOIN with exclusion. But we have to say, where the different values are not justifying those that are in table one, or table two, but not matched up between the two of them.

So here’s select star, same as the FULL OUTER JOIN we just did above, but we throw in where this favorite color is no and table, two eyes, the table twos ID is NULL. And it shows me all of those that don’t match.

So if this was something different besides colors, or people. Or maybe you had people, and instead of colors, you had desk locations and an office, and you were trying to find all of the desks that were available, and find all the people who did not have a desk assigned. You could use this whole OUTER JOIN with exclusion to get the list and then figure out well, this person has to go sit at that desk and go through the list and work it out that way.

This one you don’t use very often, but when you do come up with a reason to use it, it can be super handy compared to other ways of trying to get the same information.

Also, just reminder, you can download the join types poster at SteveStedman.com. 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 have and keep on your cube wall or near your workspace.

---

 

---

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!