Relational Data & JOIN

[00:00:00]
>> Brian Holt: So far, we've just been selecting from one table at a time. That's a frequent use case for databases, but it is a relational database. The relational there is for a reason, it's because generally you have tables that kind of correlate to each other. So a good example of this would be, I have clear tables.

[00:00:26]
I have artist, album, and track, right? I have all the tracks that are in the various different albums. I have all the albums and all the artists that made all of these albums. Now you could basically go in and say, here's this track from, Led Zeppelin album, right?

[00:00:48]
And it has the artist, Led Zeppelin, and it has the album of IV, and it has the track of Black Dog, or something like that, right? The problem is what happens if Led Zeppelin changes their name? What if they're Prince, and they become the artist formerly known as Prince?

[00:01:03]
You have to now go and update every row in your database and hopefully find all of them, right? Particularly if they have a weird symbol for a name now, which has never happened, very appropriate to talk about this in Minnesota, right? Difficult, right? And anyone that's done any amount of trying to keep two things in sync, knows that everything always falls out of sync.

[00:01:24]
That's just the facts of life when it comes to computer science. So it's a bad idea. Artists rename themselves, albums get changed, tracks get added, they get subtracted and trying to keep all that stuff in sync would be a nightmare to try and maintain. That's why we have multiple tables.

[00:01:40]
We have artists. We have albums and we have track, and they're all separate from each other, but they all relate to each other using relational data. That's kind of the point of a relational database, if I'm being super honest.
>> Brian Holt: So let's talk about one to one relationships.

[00:01:59]
One track belongs to one album, assuming you're modeling it that way, right? So you don't have a track belonging to multiple albums, it's just a track on an album. Another example of that would be members of a band, right? So you have someone like Dave Grohl that's been in a bunch of bands, right?

[00:02:23]
But he's has that one-to-one relationship for them or many-to-one, one-to-many, something like that. So I think this gets more concrete once we start writing the queries here. So I say SELECT * FROM Album, and I'm just going to LIMIT it to 5, because I don't want to see a bunch.

[00:02:49]
Now let's just do .schema of Album. So it has an album ID. It has a title, which you see these, right? These are all, as you probably know, these are ACDC songs. So you have this artist ID that corresponds to what artist wrote this, and you have an album ID which corresponds to which album this is, right?

[00:03:16]
As you probably guessed, if you go into track they have a track ID, an album ID, right? But notice that there is no artist ID on this, why? Because the artist ID is on the album, so that belongs at the album level. You don't repeat it into the track level because they all kind of correspond to each other in kind of a chain, right?

[00:03:48]
>> Brian Holt: Yeah, so going back to Dave Grohl and him belonging in multiple bands, he was in Nirvana, he was in the Foo Fighters, and he's in another band called Them Crooked Vultures. That, where you have a band member, and they can belong to multiple bands and and bands can have multiple members.

[00:04:05]
This is called a many-to-many relationship, right? I always have to look this stuff up, because it only matters when I'm teaching it. I never talk about this stuff out loud like this, right? Maybe another concrete way of showing this as ingredients and recipes. A recipe has many ingredients.

[00:04:21]
An ingredient can belong to many recipes. They have a many-to-many relationship,as opposed to a track, a track just belongs to an album, right? It doesn't belong to multiple albums. It just belongs to one album. Cool, okay, so there are these one to one relationships. Where you have two tables and they join together on just one thing and they only belong to each other.

[00:04:48]
I guess you could think of it like people being married or something like that, where in theory you're monogamous and you only belong in to each other. Normally you would just have that exist in a table, right? One, if it's one to one, then they can just be the same row on the same table.

[00:05:02]
So typically, you don't model too much in one to one relationships, but that one to many relationship with an album owning many tracks. That very common, the many to many exists like an artist belonging to multiple bands, that exists quite a bit as well. Okay, so again, we talked about this.

[00:05:21]
What if a band renames themselves, like On A Friday became Radiohead, Prince became The Artist Formerly Known As Prince. The Quarrymen became the Beatles, right? The nice thing is you have to update one row to end all of a sudden, it's fixed everywhere, right? That's one of the advantages of doing this.

[00:05:44]
>> Brian Holt: Okay, so now I have these, this is not a very useful thing for me to pass back to a user. They're not gonna know what album one is, and they're not gonna know what artist one is, right? Because they don't have any purview into your data Database, so you have to somehow join these things together.

[00:06:03]
Now, we're gonna talk about joins. We're gonna say select.
>> Brian Holt: Artist.name,
>> Brian Holt: And album.title.
>> Brian Holt: From album join artist on, and you're just gonna tell it what two things correspond to each other? How can I tell that this, artist and this album meet up together, they're gonna have some sort of mutual ID typically, which they do.

[00:06:48]
We're gonna say Album.ArtistId = Artist.ArtistID. Okay, and I'm just going to put limit five there again. And now, we get AC/DC, accept AC/DC, Aerosmith, all that kind of stuff, right? So we took back the information that was in this album table, and we joined it together with the one that was in the artist table to get one result set based on both of those.

[00:07:34]
>> Brian Holt: And let's just do .schema Artist, .schema Album
>> Brian Holt: Notice both of them have ArtistId and ArtistId, and we are joining them here together on ArtistId, right? So that's how they're the database knows to do that join correctly.
>> Brian Holt: Okay.
>> Brian Holt: Questions about that, does that make sense?

[00:08:17]
>> Brian Holt: Cool, as you might imagine, this can make things a bit easier. I'm just gonna copy and paste this because this was not interesting for me to type again.
>> Brian Holt: You can do what's called a table alias, right? So I called it Album a, and now instead of having to type out album every single time, I can just type a and same Artist b, right?

[00:08:41]
So now, a.Artist.Id = b Artist.Id. You'll see me almost always do that, because typing out the whole table name is just burdensome. And then you can select b.Name, a.Title. That's extremely helpful for you to know. You can see that still works, okay?
>> Brian Holt: And you can even use where in here, which is quite nice as well.

[00:09:11]
So I'll give her that limit five, and I'm gonna do where a. What I call artist b.Name = 'Nirvana'.
>> Brian Holt: There you go.
>> Brian Holt: And now you can do WHERE based on either table, right? Cuz you can filter on either one of those. So I could also do WHERE.Title things, yeah, Title = Nevermind,
>> Brian Holt: Right?

[00:09:57]
And that works, okay as well, you could even do and, right? All that stuff all works together. That's kind of the fun part about SQL is all the pieces kind of compose into each other.
>> Brian Holt: Yeah, this one's a pretty cool one. Let's look at this one here.

[00:10:16]
We use like. So let's say we want to find everything in our database where the title includes live. You can see there's quite a few live albums in here, which is pretty cool. You can join multiple tables together. So let's do that. So .shell clear. Let's just do this in kind of my scratchpad here, I think this is going to be easier.

[00:10:50]
>> Brian Holt: And I want to change language mode SQL. SELECT, let's just.
>> Brian Holt: So artist is probably not arduous enough for me to shorten it, or shouldn't be, but I'm still gonna do it anyway. So I'm gonna say, call it ar, ar.Name and I'll call album al. I try and give them somewhat,correlative names, right?

[00:11:19]
Instead of just ABCD, I see a bunch of people that do that as well. It just helps me read it later.
>> Brian Holt: Title and do T for track name from album.
>> Brian Holt: And then we're going to JOIN Artist. I have to call this al, Artist ar ON al.ArtistId,
>> Brian Holt: = ar.ArtistId.

[00:12:06]
JOIN, frequently, I will put multiple spaces between these cuz then it's even easier to read. Each individual kind of JOIN clause. Track t ON t.AlbumId = al.AlbumId. And then here you could just give it all of it, but I think there's 3,500 tracks in this database, so it's a pretty big result set.

[00:12:50]
So you can just ask for one album, or something like that. W're gonna do the Led Zeppelin album. al.Title = 'IV'.
>> Brian Holt: And you can see why I do this in VS Code rather than just directly into the CLI. This is a lot easier to read. I have all of my syntax highlighting I can click.

[00:13:17]
>> Brian Holt: And I just copy and paste it over here. And you can see there, I get everything in the Led Zeppelin album. And I get all the tracks back as well, which is pretty cool.
>> Brian Holt: Pretty cool, right? I think it's cool.
>> Brian Holt: And you can keep going.

[00:13:35]
I actually found out you can go 64 times. You can do 64 joins. I am not telling you should do 64 joins, but in SQLite, it is only possible to join 64 tables at a time. And I found out it's cuz they have a 8 byte registry for those various different tables,
>> Brian Holt: Cool.

[00:14:09]
>> Brian Holt: Yeah, you can just keep going. So this one has four. Let's just go copy and paste it. Which is this one.
>> Brian Holt: So this is all the Foo Fighter albums and all of their genres, cuz we pulled it out of genre ID as well. And you can see some of them they classify as alternative and punk, some of them they classify as rock.

[00:14:38]
I think that's the only two that they have in there.