SQL JOINS

[00:00:00]
>> Let's go talk about joins, more about joins. I got a great graphic off of Wikipedia. So credit to Wikipedia. You can see down here that it's attribute it to them. There are multiple different kinds of joins but we just talked about was one of them which is called an inner join.

[00:00:20]
In this particular case, it didn't really matter because we were pretty well assured that there is a going to be a match for, for everything right. So a good example of this. This top one here, this is an inner join this AB one. Every comment definitely has a user, every user well and that's the relation to the cuz we were querying for all the comments on board number 39.

[00:00:47]
And we know that every comment has a user we're sure that by the way that we set up our tables with those foreign keys, right? That's assured to us. So the kind of join that we chose here you could have chosen left join, or inner join. Both of those would have worked.

[00:01:07]
Right join probably wouldn't have worked. I don't have to think about that. Anyway, what you're seeing here is, if I have records in both one of these, that bolt that matches the where, but doesn't join, what do you want me to do with that inner join says like it must have a match or don't include it.

[00:01:25]
So if we had a comment that didn't have a user match up in the user table. In a inner joins situation, you would drop that comment. Does that make sense? So if one of the user IDs was set to know you would just not include it. Let's talk about a second this left join.

[00:01:50]
So let's take another kind of situation where a left join is actually pretty useful, if i had comments that didn't have users. And I wanted to include those comments despite the fact that they didn't have users INNER JOIN wouldn't work, INNER JOIN would eliminate those ones that don't have any users.

[00:02:06]
Left join would include them, and include anything in that querying table, that doesn't have a match in the right table, right? So, in this case, you have table A, and table B, inner join includes only the overlap. Left join also includes anything in the first table from the from table here, right?

[00:02:25]
That doesn't have a match in the beat table. That makes sense? The flip side of that is let's say I had users I wanted to include that had comments. I guess that wouldn't really work either. The one where this joins actually does make sense is if I wanna grab boards that don't have any comments, then grabbing from the right board makes sense.

[00:02:52]
And then there is some wild kind of joins where you wanna exclude things that exist in other trade tables. I don't use these ones hardly ever like 90% can be encompassed by inner joins. The other 8% come from here, 2% come from here. And then these ones just don't happen very often, but let's talk about what they are left join.

[00:03:18]
Yeah. For comment has a user ID that doesn't exist included anyway. Outer joins, which are these ones here. Full outer join basically says, if there's a match in the middle, don't include it only include things that don't have matches from either table. Sorry, that's an outer join. FULL OUTER JOINS says include everything that's this one right here.

[00:03:44]
And then there's a thing as well called a CROSS JOIN which if you know the mathematical term Cartesian product, that's what that is. Basically sit make every possible combination possible. So let's say you had a table with A, B, and C in one, and a table of D and E in another.

[00:04:01]
If you did a cross join, you'd end up with a product of AD, AE, BD, BE, CD and CE. So it makes every possible combination between the two tables. If you have, what did I say if you have 1000 rows in one table and 1000 rows in another, you'll get a million records back so be cautious when you do cross joints, okay.

[00:04:27]
If that doesn't seem super clear, it's okay. That's a lot to take in all the ones this is we're kind of getting into like set math right of how to get the overlap between two different things. Just know that. These Inner Joins are going to do most of what you want to do and occasionally left joins and occasionally right joins are going to be useful to you as well.

[00:04:46]
Natural joins are kind of more of a, I think it's a Postgres thing. I don't remember using these in MySQL, don't quote me on that. I actually don't know if they exist or not. But there's a cool thing that you can say is, hey, I know the field names match up.

[00:05:00]
So just make find ways to make this work right. So we can actually rewrite this query that I did here on INNER JOIN and just say what I can do here is I can actually remove this entire on clause and just say natural INNER JOIN like this. And it still works, which is pretty cool.

[00:05:28]
Why does that still work? It's because we call them user ID in both tables. And so it's like okay, I'm going to look for things that have the same name. If they have the same name, I'm going to make sure that they match. That's all natural join does it just it's a little intelligence around.

[00:05:46]
If you named these things consistently across tables, and you didn't like cross contaminate, right, so if you call them ID in both different tables, that wouldn't work, right? Because it would try and match comment IDs to user IDs and that wouldn't work. Because they're called user ID in both tables is like, I know what these are.

[00:06:03]
I can do a natural inner join that way. Makes sense? And just to prove my point as well, I'm pretty sure if I just say natural left join as well. It's actually not going to change anything. Because the inner join in the left join in this particular case we're gonna end up being the same thing now, I don't think right join will work but let me check.

[00:06:32]
I guess it would. Yeah it would because nothing would ever be out of the If you look at this, everything's gonna end up being the same. I mean, I guess we could do full outer join. No it wouldn't. Wait, hold on, actually it would. You can see that this stuff I have to really think about it.

[00:06:58]
This one still needs full outer and natural, full outer join. Yeah, it actually doesn't have working so you can do a full up, so in every one of these cases because everything is included in the set every single time because of our foreign key constraints, you can actually do any joint here and it'll work.

[00:07:18]
I don't know why I decided to discover this in front of you all right now, but it was a fun journey of that you can graduate. Went on me with. Suffice to say if everything is included in the set and on your joins it doesn't matter which join you're gonna choose.

[00:07:33]
What matters is when you have things that are gonna match in one table and not in the other. And some of them will and some of them won't. Then it matters is what kind of join you choose. In that situation, choose INNER JOIN, just so you know. So that's what natural join does.

[00:07:50]
That was kind of be my original point natural join just like if you named everything the same so user ID is called user ID consistently throughout our tables natural join, we'll just match setup.