TSQL Basics Part 6: ANTI SEMI JOIN – Video Explanation

This is part 6 “anti semi JOIN” 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:

Next, after the SEMI JOIN is what’s known as the ANTI SEMI JOIN. And this is simply changing the exists to not exists. And what you’re doing is you’re saying, Give me all of those people in table one that don’t match up with table two. So this would be the way if we’re looking at the favorite colors to go in and say, show me all the people who don’t have a favorite color. And then we have Johnny and Karen. And if you’re really trying to order t-shirts for your entire company, you might go push them to pick their favorite shirt color, and put it in there, so they get a shirt ordered for them.

Okay, one thing I’m going to back up to that I zoomed past here was multiple outer joins. And that’s stepping back to the previous slide, where we’re saying select from table one INNER JOIN table two on favorite color. But then we’re also going to say, that was a sort of a RIGHT OUTER JOIN them and say, left outer join table three, on the favorite food. And we’ll take a look at the combination of multiple queries there.

And you can see now, if we look at this list, those are the people who have the favorite colors. Only one of them has a favorite food, what we’re getting is that of the people who have a favorite color, only one of them has a favorite food. And well, we’ll come back to this in a little bit. But one of the things is that what happens if we specify an outer join, without actually putting a LEFT or RIGHT OUTER JOIN? Well, that’s not valid. That’s invalid syntax, because you have to specify a left or right when you’re doing the outer join. There’s a question that popped up that I’m gonna answer. Well, I’m right here. What does select one mean on your sub query when you’re looking at the SEMI JOIN, or the ANTI SEMI JOIN for that matter? Well, what that means is just selecting something back, you could say select star, and it would behave about the same way. Or you could say select ID, and would return it’s just selecting some value back. And here, I’m just selecting the value one, when it exists.

Now the difference is, depending on what you’re selecting back, this inner query might have to do more work to find the all the columns that you’re selecting back, just to determine if it exists. So hopefully that question is answered.

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 pronounce and keep on your cue ball 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!