TSQL Basics Part 13: EQUI vs NON EQUI JOIN – Video Explanation

This is part 13 “equi JOIN vs non equi 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.

Transcription:

>>> Try our JOIN Types Course Today!

Transcription:

Well, I guess the word clauses just been an all or, or isn’t all or not at all. But what’s different here is, instead of saying that we want to say where something is equal, we can join and say, where something is greater than or equal to, or less than or equal to, or greater than, or less than. And what that does, it makes some interesting queries. This is a hard one to find a good use case for, but it is something that can be used.

So let’s take a look. All we’re going to do is do a JOIN. And instead of saying equals, we’re going to do a greater than or equals or less than or equals, we run that and it’s gonna say, give me everybody from table one, and match them up the favorite colors where the favorite color is greater than or equal to t twos ID. So in this example, Steve with a favorite color of one matched up to all the colors that had an ID, where were one was less or greater than or equal to the ID. And there’s only one in that case, but then Aaron, who had a favorite color, a three was matched up to all of those favorite colors that had an ID of three had an ID of two, or had an ID of one.

Not very useful on the favorite color selection here. But there are certain cases that it might be interesting to do as well. So next, we’re going to take a look at a LEFT OUTER JOIN, with the same thing rather than an INNER JOIN, but a LEFT OUTER JOIN with the NON EQUI style JOIN, it’s going to do the LEFT OUTER JOIN, but it’s joining it where it’s greater than or equal to. So that’s where we start seeing the ones that don’t match show up rather than than those that just did match. Let me just run those two together and show that.

So here are the top one ends at Beth. And then the left outer join also includes Johnny and Karen, who have been our problems on the I joined based off of not having the fav favorite color that we’ve looked at earlier. And then you can also do the NON EQUI, RIGHT OUTER JOIN. And this is just with the less than rather than the less than or equal to. So this is saying give me all of those were able one’s favorite color in that case of one is less than the favorite color on table two. And you can see that route here, it maps to all of those. And if I threw on an order by name, favorite color, what this is going to do is show me alphabetically here Aaron has favorite colors three is going to be mapped to every favorite color that’s greater than three or every, it’s going to show every favorite color, that three is less than it. Technically speaking from how that’s phrased up there.

I’m not going to spend a lot of time on those, but it is something to know about and something that can give you some interesting results in specific scenarios, but also could cause some headaches if you’re not familiar with them.

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 pronounce 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!