Natural & Cross Joins

[00:00:00]
>> So let's look at recipes for a second. I call this recipe ID which is kind of weird. Normally you would just call this ID but I did it for a very specific purpose cuz I wanted to show you natural join which I almost never use because it usually doesn't work out and it's kind of ambiguous.

[00:00:26]
But let's do it. Select star from recipes-photos. Natural Join recipes. So this query here when you say Natural Join, it's same like, look for columns that are called the same thing in both tables, join on that. So because I have recipe ID in both tables, it's smart to say, okay, I see a recipe ID here and a recipe ID here, I can join on those things.

[00:01:13]
Nothing else is called the same so it ends up working okay but if I had title in both of them, it would try do join on both of those. So this is why I don't use Natural Join hardly ever because it's pretty ambiguous. And imagine later that this works now but my coworker later goes and adds a tile to the photos maybe they have their captions or something or body, right?

[00:01:38]
And they have captions. All of a sudden it's gonna try and match up bodies and that doesn't make any sense so I wanted to show you that that exist that people sometimes use it. It's really nice for just doing queries like this where I know they're called the same thing but I don't suggest using it.

[00:01:55]
And let's talk about Cross Join because it's silly and it makes me happy. Let's say we had a set of three things, dog, cat, chicken and we had a set of second things colors, red, yellow, blue or something like that. And you wanted to make red cat, red dog, red bird, blue cat, blue bird, like you wanted to make every permutation of all those things put together.

[00:02:22]
That is called a Cross Join and SQL can do that for you. So if I say select r.title, r.body, rp.url from recipes-photos rp Cross Join, I'll just put that into the third line, Cross Join recipes r, this is now going to go make every permutation of all these that exists.

[00:02:57]
It's a lot of rows. So despite the fact that I only have what? 15 photos and six recipes, I just made 78 rows of all of them joined together in every possible permutation. You might be asking me, why is that useful? And the answer is, it's not super useful.

[00:03:20]
Maybe someday you're gonna come across some problem and you're gonna be like, I'm so happy Brian taught me that, other than that, it's just a curiosity that does exist. But just be aware that it quickly blooms out of control. If you Cross Join a table of 10,000 to another table of 10,000, you're going to get a lot of rows.