We're working our way down the list. We've done cross join, we've done inner join, and now we're going to do outer join, which is kind of a category that contains, well, it contains three types of joins.

The outer join category contains a left outer, a right outer, and a full outer join. Now remember with the inner join, what it does is it takes a left table, a right table, joins it on the condition, and regardless of which table it's in, if it doesn't have a match, it's gone.

With an outer join, you have control over that. You can decide, I want to keep everything from the left table, no matter what. I want to keep everything from the right table, no matter what, or match everything together. If there are no matches, just give me everything back anyway, I don't really care.

Let's take a look at a left outer join to start. The syntax is very similar. We're going to do select * from users and then we're going to say, left join bookmarks. Interesting, we didn't write outer. Left join bookmarks on users.id equals bookmarks.user_id. In fact, user ID is not ambiguous. We don't have to write bookmarks there, but I do like to see it. Let's do that, and then let's throw a limit 10 on there. Let's make a little space down here and let's run that. We see Kyleigh and Waylon are back at the top. Welcome back folks, and we can limit this down.

Let's do this again. Users.id, bookmarks.user_id, first name, and URL. That seems good enough. We see Kyleigh has all of their bookmarks and then Waylon has their bookmarks following. What is different here? Well, what's different here is every single user will show up in this query. Every single user is going to show up in this query, even if they don't have bookmarks. Because we set the left table to be users and then we said left join, which means keep the left table and join in the right table where it works, but no matter what, you got to hang onto that left table for me.

Let me see if that is provable. And how can we prove that? Well, let's drop down here and say where bookmarks.id, we could pick anything but bookmarks.id is null. If we run that, we will see there are some users that have been joined to null bookmarks because they don't have any bookmarks. It just shows up as the left-hand table. In fact, the entire left-hand table is there. It looks a little sparse right now, but you'll see the user ID column is null, 'cause that comes from the bookmarks table. The URL column is null because that comes from the bookmarks table. It's not qualified, but it does come from the bookmarks table. Everything that was in the left-hand table has been preserved. You can switch this around.

If we were to do a right join, think about it. If we were to do a right join, what are we saying and are we going to see these users? What we're saying is the user's table is the left-hand table and the bookmarks table is the right-hand table. We are giving preference to the right-hand table and saying no matter what, give me everything from the right-hand table. This is going to be totally empty. This is going to be empty because the entire bookmarks table is brought back. But we might see bookmarks, not bookmarks, user ID, users.id might be null. Oh, there's one. There's one right there. There you go. So users.id, we'll do this as, let's just get rid of this. Let's just say bookmarks.id. This bookmark right here, 4 9 5 7 3 6 has no owner. This bookmark has no owner because if we look at bookmarks.user_id, we'll see in fact that that is null. Because it was in the right-hand table, it was in the right table and we said, prefer a right join, it is going to be brought back.

A left join prefers the left table and a right join prefers the right table. What about a full join? (instructor snaps fingers) It does both.

If we were to change this from right join to full join, we'll see that we still have this one orphaned bookmark, but also we still have, how did we do that? We did bookmarks.id is null. We still have all of the users that have no bookmarks as well. But if we were to drop this, we see we still have all the matches. We still have all the matches. We got Ladarius at the top now. We have all of the matches. Ladarius and his bookmarks have matched, but we also preserved the entirety of the left-hand table. For whatever reason, we preserved the entirety of the right-hand table.

You may have guessed from my little chuckle there that I don't use full outer joins hardly ever. I can't think of an actual time I've ever used it. If you have, please let me know. I would love to hear about it. Sincerely, I would love to hear about it.

Remember that an inner join is the default for a conditional join. If you want to switch to one of the outer joins, you need only to write full, left or right. If you do that, it is implied that it is an outer join. You can of course, write left outer join. You could write full outer join, but you don't have to.

Keep in mind what order you declare in your query, what order you declare your tables, and what type of join you're doing. And that will declare which tables are returned in their entirety. Unless you do full, then you get everything.