Transcript from the "Introducing JOIN" Lesson
[00:00:00]
>> So let's go write some queries for this. We're going to talk about joins like this is the secret sauce of an SQL database. This is like the reason why you want to write SQL databases. You want to have access to this power of joins and unions and those kind of fun things.
[00:00:16] I don't know if you think that's fun. I might have a warped sense of what is fun. I don't know you'll have to tell me. So let's just kind of get started. First I'm going to say first select comment ID, user ID and left comment 20 as preview, so well left.
[00:00:45] These comments are pretty long. And I don't want to display all the ones otherwise it makes it really hard for me to read. So left comment just means give me the first 20 characters cut off everything after that right. So the first 20 characters will be left If you want to take the last 20 characters as you might guess, it's right.
[00:01:07] And then to just kind of clean up the column name, it'll return this it'll project this as preview, you don't have to put that there. It just makes it easier to read. So from comments where board ID equals 39. Okay, so this gives me a common ID, a user ID and then the preview of that text.
[00:01:35] Now if I'm going to try and display this to my users, my users don't care what the user ideas, they want to know the user name. They don't want to know what the user ideas. But where's that user information? Where's the username actually live? It doesn't live in the comment more it shouldn't, right?
[00:01:52] It lives in the users table. So I guess this kind of brings us back to a fun foundational truth of databases. You wanna have one source of truth, right? So for a username, you wanna have one place where that username is stored and then you want to use that username everywhere else, right?
[00:02:11] You don't wanna duplicate the data. You want to have a single source of truth. This is just kind of a general programming kind of, principle but in this particular case, like, let's say we had user ID, instead of user ID, we had username and we were doing everything by username.
[00:02:26] That's bad because if the user ever updates their username, we now have to change that everywhere. If you've ever tried to keep data in sync before, you just know, data always falls out of sync. It's just a hard truth, the hard reality of writing code that you can never keep data in sync.
[00:02:45] So instead, we have a user table that contains all the user data and then we're gonna use the user ID as a foreign key and refer to that everywhere. And then if that user wants to update their username, they update it in one place and it updates magically everywhere.
[00:03:03] So let's make a better query. That's going to return to us that particular users username. So we're going to select comment_id, comments.user_id, users.username, time, LEFT(comment 20, AS preview, FROM, user, or FROM comments rather. Then here's where the fun comes in, you're gonna say INNER Join users. So you're gonna we're just selecting from comments.
[00:04:10] And then we're gonna join data from users. And they're gonna say on, you have to give it the clause of how it knows how to match things up. And I'm gonna say, comments User-id Equals users.user_id. It kind of sounds like if these two things match up, that means they go together.
[00:04:33] Where am I going to do it for one board. So we're gonna do board id equals 39. Where, board, id equals 39. Now you can see comments, id 524 the username that that person uses is this all right? And so now we have enough data to go back and actually correlate it.
[00:05:03] We can actually displays cuz now we know their usernames, right? And we know what time they posted it. This is enough data for us to go and actually make the display of the message board, for board 39. So let's talk a tiny second about this line right there instead of select No, so we're saying comments dot user id.
[00:05:22] We could have said users that user ID as well, they're gonna end up being the same, but it's ambiguous for us to just say user ID, we have to specify which of the same user IDs that we want. You can't be ambiguous. Whereas, comment_id, it knows comment_id only exists inside of comments, it doesn't exist inside a user.
[00:05:42] So we don't have to put the comments.comments_id. Not ambiguous. In fact, I think we could have left out username. We could have, because that's not ambiguous either. I think let's just try it. Yeah. See, we didn't have to put that one there cause it's not ambiguous. There's only one username.
[00:06:09] Yeah, but when you start doing multiple joints, right? So we did one join, but you can do multiple joints across many tables. It starts to just, just become easier to like always put out users.com and start and just kind of keep it really straight. So cool, right? Like this is how you do joins in SQL.
[00:06:32] This is kind of like one of the big powerful things about relational databases is that one user can have many comments, right? We don't have to store that user's information across many comments, whereas in MongoDB, that actually might be the correct way to do it. In SQL, we can store the users in one spot.
[00:06:48] And there are many comments in another place, right? Called a one to many relationship right? One user has many comments. And as you may imagine, there's many to many relationships. There's many one, there's a bunch of way to do these kind of relations. Right now we're just talking about one too many.
[00:07:05] And like one board has many comments as well. So you can have multiple one to many relationships across tables.