TSQL Basics Part 5: SEMI JOIN – Video Explanation

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

Okay, so there’s this thing that’s called a SEMI JOIN. And a lot of people may argue is really not a JOIN. But what what it’s doing is it’s using the exist clause in your query to just find those things from a specific table that match. So this is like, this is similar to an INNER JOIN. But it’s only returning columns from table one. The other difference is with an inner join, you can get duplicates, like that’s specifically why I put in those duplicate colors there on the favorite color is that you can get duplicates. And in the case of ordering t shirts, well, you might end up with one person getting two t shirts, because they had two colors, not in this case.

What this does is it will only return each row from table one once. And it will only show those that have a match in table two. And semi is Latin for half. So a semi join is like a HALF JOIN. And we’ll take a look at how that one works.

So here’s the SEMI JOIN. And we’re selecting everything from table one. And we’ll if we just run that you can see that there’s all the people, there’s all eight of them, but only where there’s a value that exists in table two, that matches the favorite color. So when we run the whole thing together, we get all of those people who had a favorite color but not duplicates of them.

Now, you might look at this and say, well, wouldn’t I just change this to say instead of where exists on this sub query, I could say where that favorite color foreign key is not? No, you could. But the difference is this is actually going and confirming that it exists in that other table rather than someone who might have put a number in there that doesn’t exist in that table.

Also, just a 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 print out 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!