Other Types of Joins

[00:00:00]
>> Brian Holt: You and I have been writing, I'm just gonna copy and paste this in here, what are called INNER JOINs. An INNER JOIN, if you're thinking about a Venn diagram, let's actually even just open this that takes you directly to Wikipedia, which is where I got that image.

[00:00:18]
Open image in new Tab.
>> Brian Holt: So think of a Venn diagram. You have two tables, an INNER JOIN or just join, which is what we've been doing. Some people say always write INNER JOIN to be clear. To me, it's very obvious that just join means INNER JOIN. So that take that as you will.

[00:00:43]
And it's saying if something that exists in table A and exists in table B, then give me that result set. But if it only exists in A or if it only exists in B, do not give me that result set. So a good example of this is in our data set, I know for a fact that there are no postal service albums, which is a damn shame because there's only one, right?

[00:01:11]
So if I did an INNER JOIN between track or no, artist and album, we would get nothing from postal service back because it exists in the artist table. It does not exist in the album table. So you're saying leave out anything, leave out any artist that doesn't have any albums, right?

[00:01:31]
That's what an INNER JOIN is. That's what joins do by default. I would say it's one of the more common ones you're gonna use, if not the most common one, it's why it's the default one.
>> Brian Holt: But that's not always what you want. Sometimes you, let's say we do want, I want every artist in the table and I also want all the albums that belong to them, but still include all the artists that don't have any albums, right?

[00:01:55]
That would be called a LEFT JOIN, which do I have that in here? I do, it's this one. Left joint says from my from clause, which is what the left is in this particular case, right? From table A, which is this one, which if you're drawing a bend diagram would be on the left side, that's why it's called the LEFT JOIN.

[00:02:15]
Include everything that's not in the other table and also give me everything that is in the other the table as well that has something join on, right? So that's the part of the Venn diagram that you get back. As you might imagine, there is a RIGHT JOIN and the right is the table B, the table that you're joining into the result set, right?

[00:02:36]
So this would be saying, give me all of the orphaned albums. It's right, maybe I don't have an artist to represent this album, but give me them anyway. Maybe the way that you represent various artists would be to leave it null. That's a terrible way to model this, but you could, right?

[00:02:51]
In which case a RIGHT JOIN, in that particular case would be useful to you. That's 99% of what you want out of joins is between these three, there is a full join, FULL OUTER JOIN, which is this one. So, this would be saying, I might have artists with no albums, I might have albums with no artists.

[00:03:13]
Give me everything that is a FULL OUTER JOIN is like, I want something represented for everything in both tables. And then for everything else, you kind of start getting into weird places. It's like a LEFT JOIN where the B key is null. That will give you just things that are in the left table and none of the intersection in the middle.

[00:03:33]
This is quite rare. The only time I end up doing joins like this is when I'm trying to clean up data. It's hey, what exists in this table that should have something in the other table but doesn't, right? Then you get into these kind of scenarios where we're trying to do these like LEFT JOIN and where B key is null, right?

[00:03:53]
Because that would be indicative that it didn't exist in the other table. Same thing here. And that's, that's basically it, yeah. There's like this FULL OUTER JOIN where the A key is noll and the B key is null, which is basically gimme everything that doesn't exist in the middle, right?

[00:04:09]
I can't, I have not done that before, but it's there you quit. There's also a CROSS JOIN, which is like the, you never wanna do that, right? If you had like, a matrix of one, two and three, and you cross, joined that to a different table that had four or five, six, it would give you every possible combination of all of those things together, right?

[00:04:32]
It's the cross I'm it's been forever since I've had to do matrix math, 20 years at this point, but it's. It's a matrix math operation where, basically you're giving me the cross product of two different matrixes. That's what a cross join is. I don't even know if SQLITE does it because it's a nonsensical thing to do.

[00:04:53]
>> Brian Holt: Okay, so again, INNER JOIN or join are the same thing some people tell you always write the inner I think they are silly. I don't think you need to. I use INNER JOIN the most. I use LEFT JOIN quite a bit. I occasionally use RIGHT JOINs. Everything else is I fairly infrequently used.

[00:05:14]
Same with the self joins that we were just talking about.
>> Brian Holt: Yeah, okay, so,
>> Brian Holt: Yeah, if we do an interview, Snow Patrol, another band that I liked, first time I ever crowd surfed was at a Snow Patrol concert, which is a weird thing to say, but it's true.

[00:05:41]
If I do this one, I know for a fact that they haven't put any of the albums for snow patrol in there, so we should get a set of zero, right? However, if we do a RIGHT JOIN because the artist is the table being joined in, right? So that's the right side of the Venn diagram.

[00:05:58]
We should get Snow Patrol.
>> Brian Holt: With nothing there right?
>> Brian Holt: Now you might ask me, could you do it the other way and make it a LEFT JOIN? The answer is absolutely. You could totally make this. Let's just do it to prove my point here. So if I, switch artist and album A
>> Brian Holt: I'm not gonna fix spacing, you know what I'm talking about, though.

[00:06:34]
And then I make this a LEFT JOIN.
>> Brian Holt: It's not patrol, right? Cuz then now this is the left side of the table and the what's being joined in is the right side of the table, right?
>> Brian Holt: In this case, maybe an index might affect this sometime, but I can't really imagine there's really a beneficial way of thinking about this one way versus the other.

[00:07:03]
Maybe on large results sets you might start getting into which one has more rows might be better to select second, maybe which one's better. And honestly, it's so hard to kind of predict what, how that's gonna be better, you'd have to do. Do a performance impact on both of those, and I'll show you how to do that here in just a bit.

[00:07:25]
In this case, we're only talking about a couple 1000 rows. Makes properly, zero difference of if which one's in the from and which one's in the join. And frequently, you don't have a choice based on how your query is structured. Yeah and let's do an outer, right? Outer join, that's kind of an interesting one.

[00:07:48]
>> Brian Holt: So, looking at our table here.
>> Brian Holt: We are gonna do,
>> Brian Holt: RIGHT OUTER JOIN that outer is just optional, right? It's the same thing as this.
>> Brian Holt: This is all of the artists that don't have albums, which is what that query did, which is this one. And again, you can leave the outer out, right?

[00:08:30]
Totally optional some people like to have it there. It's always implied. If it's a RIGHT JOIN, it's you can't. There's no RIGHT INNER JOIN. Is nonsensical, right? That's not a thing, right? So if it's a RIGHT or a LEFT JOIN, it's a LEFT OUTER JOIN or a RIGHT OUTER JOIN, and you can just omit them, which is why they let you omit them.

[00:08:54]
>> Brian Holt: Okay,
>> Brian Holt: Let's talk about NATURAL JOINs, I don't care for these. Some people like them. Those people are kind of wrong in my opinion. B.name, A.title. From album A NATURAL JOIN B limit 5
>> Brian Holt: NATURAL JOIN artist B.
>> Brian Holt: Notice I have on clause. NATURAL JOIN is basically telling SQLITE figure it out.

[00:09:42]
You go, figure out how to join these tables and give me the result set. And the way it figures it out is if you have something called the same thing in both tables. So again, schema artist and schema album because of the way that the chinook developer chose to name these columns.

[00:10:03]
They're both called artistid, and that's the only thing that they share in common. So NATURAL JOIN works here. If you name your stuff, well, you can use NATURAL JOINs that way. I think this is too magical. I think it's a prone to break. What happens if you rename something?

[00:10:23]
What happens if you add notes to both of them, right? All of a sudden it's going to see notes in both of this cool. I'm gonna join on those too, right? It's a bad idea. Yep, and I have a longer one here, if you want to see it with the track as well.

[00:10:41]
It writes really well, right?
>> Brian Holt: You know what actually, I think this might even be wrong. I hadn't even realized that. It is wrong you know why B.name and track and dot name, that's hilarious. I hadn't even thought about that was gonna happen. So it's only going to give you back where the artist's name and the track, name are the same.

[00:11:23]
>> Brian Holt: It is, how funny. Well, there you go. This is exactly the reason to not use because that query seems totally fine, right? Seems totally fine to write, and it is not correct. It's not what you were looking for.
>> Brian Holt: I love accidentally proving my point.