Udemy

LEFT vs. INNER vs. RIGHT JOIN

A free video tutorial from Maven Analytics
Empowering everyday people with life-changing data skills
Rating: 4.6 out of 5Instructor rating
39 courses
1,231,693 students
LEFT vs. INNER vs. RIGHT JOIN

Learn more from the full course

SQL 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 September 2023

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]
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.