Left, Right, & Outer Joins

[00:00:00]
>> Lessons, we're already here, and we have this. What if we wanted to include it? What would we do? In the FROM table, so you do left, run one. Say, this is the thing that I was getting messed up on, left to right, it's right. So we do what's called a RIGHT JOIN, which basically means that anything that exists in the recipe table.

[00:00:31]
Sorry, the, yeah, the recipe, that's fine. Anything that exists in the joining table, not the FROM table, the original table. I wanna include anything that doesn't have a corresponding thing in the other table, you would do a RIGHT JOIN to include things from the joining table, To right table.

[00:00:51]
As you can see, I can never keep it straight in my head, so I normally have to do both to make sure I'm doing the correct one. But you can see here, now this is being included even though it doesn't have a photo to include. You can imagine this being useful if we're trying to include a results set for all of our recipes.

[00:01:07]
We wanna include recipes that don't have photos, that's totally fine that they don't have photos. They should still be shown on the site and will show some default thing. That's what a RIGHT JOIN is useful for. For some reason we had photos that didn't have recipes. Then we need to use LEFT JOIN, right?

[00:01:30]
If you wanna include both, I think it's full join, if I remember correctly. Not generally one that I think a lot about, but it's out there. As I said, I think 95% of what I do is inner joins. The last five, let's say 4.9% of it is left and right and 0.1%, I'd have no idea what I'm doing.

[00:01:55]
One thing that I didn't show you here, technically, it's a RIGHT OUTER JOIN. So you can actually include that OUTER there, but the word OUTER is optional, as oppose to INNER JOIN, right. Which is only include everything that matches up. There you go. So I was right, it is a full OUTER JOIN.

[00:02:16]
If you wanted to include everything, this is not gonna be any different than our RIGHT OUTER JOIN. Because there's nothing that exists in photos without recipes because that wouldn't make any sense, we wouldn't have photos that don't have recipes. But if you did, FULL OUTER JOIN would include both.

[00:02:35]
In other words, FULL OUTER JOIN is LEFT plus RIGHT JOIN or LEFT plus INNER plus RIGHT, whatever you wanna call that. So let's go look at this really cool graphic I got from Wikipedia, that's helpful to look at. This one right here, you can think of it like a Venn diagram, this is INNER JOIN, most of the time what you want between table A and table B.

[00:03:04]
A RIGHT JOIN includes everything in the middle plus everything that's not included in the LEFT table that exists in the RIGHT table. There's a LEFT JOIN here and these top ones are most of what you're gonna do most of the time. If for some reason you wanted to include, you wanted to use a table to exclude something in there, Maybe you have, a user that has chosen to ignore a bunch of other users in their timeline, right?

[00:03:36]
So you have this table that user A has ignored user B, C, and D, and you're trying to make their timeline. So what you would basically do is you do this right here. This RIGHT JOIN, and then do a right key is null, so you're just including the outer side of it.

[00:03:55]
So you'll exclude all those people in the middle, the users that were ignored by user A, and get everyone to the rest of your timeline. I'll tell you it kinda do that ignore list sort of pattern, right? Or deny list or whatever you wanna call it. To make sense, that's what you would do, use something like this.

[00:04:17]
There's a FULL OUTER JOIN where basically you can say, exclude everything where they overlap. This is the FULL OUTER JOIN right here. We just looked at that, rather and vice versa there. So that's kind of the full possibility of JOINS, again, this one's the most common, these two are a little bit more common.

[00:04:41]
Fathom to the only example I can think of to do one of these, right.? And yeah, cool any questions?
>> I'm still a little confused. So RIGHT JOIN returns a bunch more stuff because it's the INNER JOIN plus, what again?
>> So as you're just saying, if you have a world that doesn't have something to match with it, what do you wanna do?

[00:05:09]
Do you wanna include the right side? Do you wanna include the left side? Do you wanna include neither? Inner join just include the inner part of the Venn diagram. Left and right is saying take from left or take from the right, and then full is include everything.
>> And the left and right is basically the left side of the ON or the right side of the ON?

[00:05:25]
>> Left is gonna be stuff from the FROM, right is gonna be stuff from the ON. But I have to check that every time, cuz I can never remember. Usually is from both queries we see that one. [LAUGH]
>> Is that opposite, though, cuz-
>> It might be, C.

[00:05:48]
>> It may be opposite
>> [LAUGH]
>> Because we found on the recipes photos,
>> And we don't wanna include those.
>> Yeah, so it was the, my God, I'm confusing myself never mind.
>> Yeah, I'm also confusing myself too, and you are not helping, I'm just kidding.

[00:06:07]
>> [LAUGH]
>> [LAUGH] I'm just kidding.
>> But there where you could switch the FROM, and then if you switch FROM and ON, look at the one you listed first. You could still do FROM recipe instead of recipe_photos, and then LEFT JOIN on recipe_photos, and you just switch the join direction.

[00:06:28]
>> Yeah.
>> Do the same thing.
>> Correct.
>> Okay.