SQL JOINs | INNER vs LEFT vs RIGHT vs FULL OUTER | Clearly Explained | Beginners Start Here!

A common SQL interview question is to describe the different join types within SQL. Alice Jao here from Maven Analytics

SQL. Alice Jao here from Maven Analytics and in this video I'll be walking through the four main types of joins within SQL.

There are four basic join types within SQL. Inner, left, right, and full outer.

SQL. Inner, left, right, and full outer.

Now the way that an inner join works is that it returns records that exist in both tables. So, so far we've been

both tables. So, so far we've been working with the happiness scores table and the country stats table. Now, if we were to combine these two tables using an inner joint on the country column and our output, we would see happiness

scores and country stats for countries that exist in both tables. This is often represented as a vend diagram which you see on the top left there. And then that common area in the middle represents the countries that both tables have in

common. Next, we have a left join which

common. Next, we have a left join which is going to return all the rows in the left table and then any matching records from the right table. So in the case of our happiness scores table as our left table, it's going to return all

countries that exist in the happiness scores table and then it's going to bring in the country stats just for those countries in the happiness scores table. A right join is the opposite of a

table. A right join is the opposite of a left join if we're joining happiness scores with country stats. In this case, it's going to return everything from the country stats table. And then it's only going to pull in happiness scores for

countries within the country stats table. Finally, you have the full adder

table. Finally, you have the full adder join and that's going to return all records from both tables. So all the countries that exist in both tables as well as just one of the tables. Now even

though there are four basic join types, some are used much more often than others. For example, inner and left

others. For example, inner and left joins are the most common. This is what you'll see in a lot of SQL code. Right

joins are less common. What often

happens with this is people will switch the order of their tables and then use a left join instead. And then finally, some of these joins are actually not available in all tools. So while inner

and left joins are supported in all RDBMS's, right and full outer are not.

So for example, SQL light doesn't support right joins. You have to use a left join instead. And then both MySQL and SQL light don't support full outer joins. So in these cases, based on

joins. So in these cases, based on whatever your end goal is, you can rewrite your right and full outer joins as a combination of inner and left joins. Now let's see what these join

joins. Now let's see what these join types look like in action. Let's say

that we have this left table here with a country column and a happiness score column. And then we have a right table

column. And then we have a right table with a country column and a continent column. You can see that both these

column. You can see that both these tables have this country column which is what we're going to be joining them on.

And then also our left table has five rows and our right table has three rows.

Now just by scanning the country values here, you'll notice that both tables don't have the exact same countries. The

only country that they both have in common is the United States. Okay. Now

with these tables, let's try to do some joins. So the first thing we're going to

joins. So the first thing we're going to do is an inner join. And you can see with an inner join, because we're joining on the country column, the only country that these two tables have in

common is the United States, which is why we only see the United States in our results. Now, let's say we did a left

results. Now, let's say we did a left join instead. In that case, you can see

join instead. In that case, you can see we have all the data from our left table. So we had those five rows in our

table. So we had those five rows in our left table, and you can see those five rows here. And then anything that

rows here. And then anything that matches a country in the right table, that data will be pulled in. So you can see North America was pulled in here.

And then anything that doesn't exist in the right table gets filled in with null values. Now let's do the same thing, but

values. Now let's do the same thing, but we're going to do a right join instead.

Now this is the opposite of the left join. You can see everything from the

join. You can see everything from the right table is now kept here. So you can see n equals 3 in our right table at the top and then it also equals 3 in the output of our right join at the bottom

here. But then any countries that don't

here. But then any countries that don't exist in the left table will just show up as null values. And finally, this is what the output of a full outer join would look like. Hey there. If you're

enjoying this tutorial, just so you know, it's actually a part of my full 13-hour advanced equal querying course at Maven Analytics. So, if you want more hands-on practice and fulllength lessons, check out the link in the

description below. All right, now let's

description below. All right, now let's get back to the tutorial. Now, with

this, we're displaying all the data from both the left table and the right table.

So in the left table we had five countries and in the right table we have three countries. And you may be

three countries. And you may be wondering why the result of this join table isn't 5 + 3 or 8. And the answer is because it's first listing all the countries that exist only in the left

table. Then it's listing all the

table. Then it's listing all the countries that exist only in the right table. Then it's listing the country

table. Then it's listing the country that exists in both tables. And that

ends up being just seven total rows. Now

let's go through all these join types within a demo as well. To kick off this demo, let's copy our query from the previous demo. Now, if I run this here,

previous demo. Now, if I run this here, you can see that we have data from our happiness scores table, as well as our country stats table. Now, I'm going to add back in one column of data here to

help illustrate some points later in this demo. So, right now, these three

this demo. So, right now, these three columns are coming from the happiness scores table. I'm also going to add this

scores table. I'm also going to add this CS country here, so we can see the country name from the country stats table. So once again we have these

table. So once again we have these columns here from the happiness scores table and these columns here from the country stats table. Now in this case we did an inner join and that means that

every country in the left table will match this country value in the right table. And with inner joins there are no

table. And with inner joins there are no null values. So if I scroll all the way

null values. So if I scroll all the way down here you'll see that there's going to be a country match for each row. Now

I can double check that as well by adding on a wear clause. So I can look within the country column within the happiness scores table and see if there are any rows that have null values.

And if I run that, you can see that there are no rows with null values. And

I can do the same with the country stats table. And again, there are no rows with

table. And again, there are no rows with null values. And the reason for that is

null values. And the reason for that is because I did an inner join here. Okay,

so that's an example of an inner join.

Next, let's look at a left join. So I'm

going to copy that down here and replace inner with left. And if I run that, then things look the same here right now. But

with a left join, what happens is I keep all the country values in my left table and then any countries that don't match will show up as a null in the right table. So to check that, let's add on

table. So to check that, let's add on our wear clause again. So if we look at our happiness scores table, specifically that country column, and I do an isnull,

we should see no values because with a left join, everything from our left table is kept. Now if I change this to CS and I look here now you see that

there are a bunch of null values here and the reason is because with a left join all my countries stay here and then if there's no matching country in my right table those show up as null

values. So this was an example of a left

values. So this was an example of a left join. Now let's copy this down here and

join. Now let's copy this down here and look at an example of a right join. Now

if I do this right join and I keep this CS country is null then you can see nothing shows up here because now everything in my right table or my

country stats table will be present but then if I change this to HS you'll see all these null values here and the reason is because these countries are present in my country

stats table but they don't exist in the happiness scores table so they show up here as null. Now, my SQL doesn't support full outer joins, but if you could do a full outer join, the results

would be the combination of these three queries here. This one returns rows

queries here. This one returns rows where the country matches in both tables. This one returns situations

tables. This one returns situations where the countries in the left table but not in the right one. And then this one returns countries that are in the right table but not in the left table.

And then if you stacked all these results on top of one another using unions, then you would get the result of a full outer join. And that's the difference between an inner, left, right, and outer join. Now, one more

thing I want to cover is how I often manipulate these queries to find the countries that exist in one table and not the other. So, to do that, I'm going to copy these two queries down here.

And what I want to do is find all the countries that exist in the left table, but not the right table. So, to do that, I'm going to run this query here. And

you can see these are all the countries that exist in the left table but not the right table. And from here I'm just

right table. And from here I'm just going to replace the select clause with a distinct country. And if I run that, you can see that these countries exist

in the left table but not the right table. And now I can do the same thing

table. And now I can do the same thing to figure out the countries that exist in the right table but not the left table. I'm going to do a distinct

table. I'm going to do a distinct country, but this time it's going to be on my right table's countries or my country stats countries. And if I run this, you can see that these are the

countries that exist in the right table, but not the left table. So, that's a practical way that you can use joins to figure out which values exist in one table versus another. If you're looking

for more ways to level up your SQL skills, check out our self-paced courses, guided projects, and portfolio showcase at Maven Analytics and create your personalized learning plan for free. Thank you so much for watching,

free. Thank you so much for watching, and as always, make sure to like and subscribe for more data content just like this. See you in the next one.

like this. See you in the next one.