This is part 3 on left 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.
>>> Don’t get your JOINs confused – See Our JOIN Types Course!
Transcription:
Now we’ll take a look at a left outer join. And when we look at this, a lot of the time you get confused as to what left means. And then we’re gonna take a look at a right outer joins. So what what this means is, and the only thing I’ve changed in here, as I’ve written this query between this one and the previous one is I changed the word inner, to simply be left outer, and it’s joining the same tables. And what it’s going to do is it’s going to return all the rows from table one. And if there’s a match in table two, you will get the match. Otherwise, the table two columns will be shown as nulls. So for instance, in the example we looked at a minute ago, there were two people, Johnny and Karen, who did not have a favorite color. And they got left out when we did the inner join. But when we do this, with the left outer join, they get included. And we’ll take a look at that in the code right now. So all I did was change the word enter, from the above query to say left outer join. And you can see that the first batch of rows that we got back in the result set here, the ones I’ve just highlighted, are the exact same thing that we saw when we did the inner join. But the two at the bottom, Johnny and Karen, who were missed on the inner join query got included, because of the use of the left outer join. And with that, because there’s nothing they matched in the favorite colors table, the columns that come back from the favorites, colors table, just get simply put in there as null. That can be handy. For instance, if you had a list of employees, which then that’s what this was, and you were going to print up like company shirts and you wanted to get their favorite color on their company shirt, you could run a query, and maybe the answer was if somebody doesn’t have a favorite color, they get a green shirt. In that case, if you did an inner join, you would have missed Johnny and Karen and they wouldn’t have got their t shirts printed this way, where if you do the left outer join, you can include those people, even though they don’t have a favorite color. And I’ve seen so many times where somebody will have a set of data, they do an inner join, and then they wonder where all these other people are. Why are these people missing? Well, it’s because they did an inner join to something that was an optional table or optional fields. And they got dumped out of the result set where the left outer join would allow them to be included. 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 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!