So far in this section, we've spent most of our time covering inner joins. So now we're going to break out and start getting into outer joins. As we go about doing that you're going to be able to see what those differences are and you should also be able to see when you want to use one join type over another one.

So let's first start by just joining two tables together and giving us a base case and say select all from Guides G. Then we'll say we'll just do a regular join so we are going to say users u and then we're going to say on G Dot guides users ID equal u user's ID. Now if we run this is going to return all of our guides and the users that wrote each one of them.

I also added some more sample data just so we could have some other things that we could see besides just the three users. So we have two more users and two more guides that we can see here. So now that we have kind of a base case let's talk about what these differences are.

So here you can see that we have all of our users we have duplicate users because every single time we have a guide which we have eight guides right here there needs to be a user associated with that. That's always going to be the case with any kind of query. So that is what we have here.

Now the difference is and this is the key thing to remember the difference between inner and outer joins Is that right now we don't list all the other users. So if a user has not written a guide and we do a join it is not going to show that user. So that is the key takeaway here is that an inner join is not going to bring in any users that have a null value for guides and if you do want to have that which there are times when you will then that is what outer joins are for.

So now let's start breaking that out. So I'm going to say all of this so everything here, for the most part, stays the same except now I'm going to say right join. Technically you could say right outer join but the syntax is clear there is no such thing as a right inner join. So again right join works exactly the same way as before. So now I'm going to select this and run it.

Now you can see an incredibly different result set.

So we have all of the same parameters we have guides ID all the way through guides quantity user's ID through their email but now you notice that it lists all of the users even if they have not written a guide so that is the key takeaway here is an inner join just a basic join is going to ignore any values that are null, where a RIGHT JOIN is going to say OK here are my guides and I'm going to show those but I'm joining this with users in because it's a right join in because we have users right here I'm going to still display I'm still going to bring back all of the users.

So that is a critical thing to know right here is that's what the difference is if you are going through interviews and SQL is something that you're going to be doing quite a bit of.

One of the most common interview questions is what is the difference between an inner and outer join now with all of that being said the right join has a cousin called the left join. Let's copy this and let's change it to left join. Now let's see exactly what this does. So if I return now we're pretty much back to where we were with this regular join.

So what are the key differences here? It is the order that it joins the table and how it is going to treat values that are null. So because it's a left join here it looks at the left table which has guides here in the query above it looked at the right table which was users. And if you don't trust me let's just take a look at this.

We can play with it if I switch the order of these you remember a few guides ago I talked about how the order was important. Well, the order is not important for inner joins but the order is critically important for outer joins. So now let's run the same query that before just brought back 8 results on that.

Now it brings back a few thousand results and if you come down you'll see it brings every single one of our users. So this is one of the most critical things when it comes to understanding joins is first to understand exactly what the difference is and when you want one over the other. I'm going to reverse these just so in the source code that you have you will have the correct one. If I run this again we're back to not having any null values.

So this is something that may not actually make a ton of sense the very first time you've seen it if you've never even heard of outer and inner joins or right and left joins this may seem very odd and it may not make sense. So that is perfectly fine I remember when I was first learning about SQL, joins kind of made sense I understood the reason why I wanted to be able to use SQL was to join tables together so the concept of joining them and mapping them together.

That part made sense where I started to get really confused was when the whole concept of the inner versus the outer join came into play because that just kind of seems a little bit intimidating if you've never done it before. So I definitely recommend playing around with this code write it out. Write each one of these commands and play with your own data to see the results that you get back.

Now the main thing to use here. The main thing that I want you to remember is that an inner join does not allow for any of those values whereas a right in LEFT JOIN are simply ways of allowing you to pick and choose which side of the or which table you want to essentially be the focal point.

So right here as you can see let's with our left join we're saying that I want guides to be the focal point of this table and I don't really care about any users unless they're associated with the guide whereas right here it's the exact different when it's a right join.

I say the only join or the only table I care about is the users table. Bring me any guides that happen to be there but I still need to be able to see all of the users. In a nutshell that is the best way to understand joins inner versus outer joins in SQL.

Code

USE devcamp_sql_course_schema;

-- Inner Join
SELECT *
FROM guides g
JOIN users u
ON g.guides_users_id = u.users_id;

-- Right Outer Join
SELECT *
FROM guides g
RIGHT JOIN users u
ON g.guides_users_id = u.users_id;

-- Left Outer Join
SELECT *
FROM guides g
LEFT JOIN users u
ON g.guides_users_id = u.users_id;