LEFT vs. INNER vs. RIGHT JOIN
A free video tutorial from Maven Analytics
Award-Winning Analytics & Business Intelligence Training
4.6 instructor rating • 22 courses • 507,351 students
Learn more from the full courseSQL for Data Analysis: Beginner MySQL Business Intelligence
Learn SQL database querying w/ MySQL Workbench. Perfect for SQL beginners and first-time coders.
04:27:25 of on-demand video • Updated February 2021
- 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 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 and actor award is on the right. The only difference between the three queries is the type of joint we have left inor 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 a 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 no rows here, we just don't have those results at all when we use inner join. Now let's take a look at Wright, join Wright, join returns, all values from the Wright 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.