TSQL Basics Part 4: RIGHT OUTER JOIN – Video Explanation
This is part 4 “right outer 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 on my YouTube channel.
>>> Try our JOIN Types Course Today!
Transcription:
Next, we have a Right Outer Join. And the difference between a left and a right OUTER JOIN is that it returns all the rows from table two instead of table one. And if there’s a match, it shows the same thing you would see on an inner join. But if there’s not a match, it shows the table one columns that are shown as nulls. And all that we changed here was the word left outer to be Right Outer. And what left and right implies is that the first table is considered the left table. That’s table one, in this case, and the second table is considered the right table. That’s table two. In this case, let’s take a look at how that query differs. Scroll down, and the only thing that changed between this one and the previous one is the word left was changed, right. And you can see now, the bottom set of results down here is exactly the same as what we saw on the inner join. But we have these three colors over here, pink, mauve and orange, that are not mapped to a specific user. Now, those got overlooked in the previous two queries, because they were not included in the inner join, or the left outer join. But with this, if you wanted to say give me all of the colors and show which people have that color as their favorite color, the Right Outer Join, which we’ve used here would be more useful in being able to see the entire list of the colors. Now one of the questions that always comes up is why a Right Outer Join versus a left outer join couldn’t you just reverse the order of the tables and always use a left outer join. And if you wanted to do that, you certainly could you could go and grab this query and switch table two and table one around and make this a left outer join. And it would absolutely work. The same way that we just did with the Right Outer Join there are the columns might show up in a little bit different order because of which ones first or second. But that can easily be managed by by specifying the columns in your query. But where it gets interesting is not just when you’re joining two tables. But when you’re joining three tables, or four tables are 10 tables. Sometimes that left outer join is going to be the only way to get what you need based off of all the other tables in the query or sometimes the Right Outer Join is going to be the only table to achieve what you’re looking for there. So they’re basically the same concept. It’s just determining which table is going to be treated like the inner join and then which table is going to have those extra nulls added in depending on the left or the right side. 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.
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!
There’s actually a bit of history here. When this was proposed to the ANSI standards committee, the submission looked a lot like your Venn diagrams poster. But the correct terms are “preserved” (contains only matched rows) and “unpreserved” (creates padded rows). The standard has syntax for all possible combinations, allows for self joins, and some weird ones that nobody uses and most people haven’t implemented. OUTER UNION anyone? As Dave McGovern once said, “the committee never met a feature, it didn’t like.”
The LEFT OUTER JOIN is the most commonly used because we read from left to right and thus intend to organize any text that way. This is also how operator precedence is determined for the infix JOIN operators. This is why it’s always a good idea to use parentheses if you have the unusual situation of a lot of mixed joins in an expression.