LEFT vs. INNER vs. RIGHT JOIN

Maven Analytics
A free video tutorial from Maven Analytics
Award-Winning Analytics & Business Intelligence Training
4.6 instructor rating • 16 courses • 381,653 students

Learn more from the full course

MySQL for Data Analysis - SQL Database for Beginners

Learn SQL for Business Intelligence & Big Data Analytics w/ MySQL Workbench (apply to SQL Server, Oracle, PostgreSQL)

04:26:13 of on-demand video • Updated September 2020

  • Write SQL queries to explore and analyze databases with MySQL Workbench
  • Learn how to use SQL to extract, sort, filter, and group data quickly and efficiently
  • Analyze data across multiple tables with powerful MySQL JOIN statements
  • Solve practical hands-on assignments and real-world business use cases
  • Master unique tips, tools and case studies that you won't find anywhere else
  • Get LIFETIME access to resource files, quizzes, assignments, and expert support
English [Auto] OK 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 joints 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 an 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 matches found so take a look down here. What we see is actor first an 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 no's 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 nose 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 I.D. three and four and then having no 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.