LEFT vs. INNER vs. RIGHT JOIN

Okay. Now that we've talked about left inner and right join, let's hold them up side by side and compare outputs so that you can really understand the little nuances between the joins. As you're getting up to speed. I would suggest going to the course materials, finding this slide and printing it out. I think this side by side comparison is the best way to understand what's happening with each of these. So we've got two tables, the actor and the actor award table. And in each of the three queries, actor is on the left and actor award is on the right. The only difference between the three queries is the type of join we have left inner and right. Let's take a look at what happens to the results with each of the types of queries. When we use left join it returns all values from the left table and then brings in any values from the right table where there's a match. It returns null for the right table where no match is found. So take a look down here. What we see is actor first and actor last. These fields are always populated because they're coming from the left table. But then awards first awards last and awards can be null because it's coming from the right table. And some of these actors have never gotten an award. Now let's take a look at inner join. So inner join doesn't have any nulls in here, but notice some differences from the first query results. Here we have Actor one, two, three, four, five. Here we seem to be missing actors three and four. Notice here that they had nulls for the award values. So in the inner join, when no match is made, the results are excluded from both tables. So instead of returning actor first for actor ID three and four and then having null rows here, we just don't have those results at all when we use inner join. Now let's take a look at right join. Right. Join returns all values from the right or second table and then any records from the left where there's a match. So we see some interesting phenomena here where there are some people in the awards table which for whatever reason, didn't show up in the actor table. So in this case, these people in the award table never would have made it into the final results when we were using left join or inner join because we were starting from actor and there's no corresponding actor in that table. But when we're using right join, we're starting with all the data in actor award and then bringing in data from actor if there's a match. So take a look at these queries. Take a look at the results down here. And like I said, I would recommend printing this out. I think this type of a side by side comparison is going to be the best way for you to get your head around this stuff.