Current section: SQL 6 exercises

Working with Joins in SQL


00:00 For this next exercise, we want to display the user's profile photo. And that information is stored in a different table. So we're not going to get it from the user. We need to somehow get this other table in the same query. Now, we could do a separate query and say, OK, select all the user's images that match.

00:19 And that would be highly inefficient. I do not recommend this. And so what we're going to need to do is what's called a join in SQL. And so let's talk about joins. When I was looking for something to illustrate how a join works, I was looking for a Venn diagram. I saw this exact thing.

00:36 And I also ended up seeing a link to this article. And I was like, oh, yeah, Venn diagrams don't do a good job of explaining joins properly. And so what does do a good job of explaining these joins properly is this diagram right here.

00:53 OK, so we've got this type of idea of these two tables and these different joins and what those result in. So here is table numbers and table letters.

01:08 And the color, it's the matching or the condition for those things. So let's talk about the result of these two things. So let's assume that the numbers represent our users and the letters represent our images in our case. So if we did an inner join, what that means

01:27 is we're going to take the things that have a match on both sides of the table. So there's no black on the left side of the table. Or there's a black on the left side but not on the right side. So we're not going to get that in our results. There's an orange on the right side but not on the left side. So we won't get that in our results.

01:45 And so what we end up with is just the stuff that matches between the two of them. And then we have this left join. And what this is going to do is it's going to say take everything on the left. And if there's something on the right, then bring that one along. If not, then just leave it empty. So that's the case here. There's no orange on the left, so that doesn't show up.

02:04 There is a black on the left and not on the right, but that's fine because it's a left join. This, spoiler alert, this is what we want. We want all of the users. And if they have an image, then we'll take it. And then we have our right join. So this is the opposite of left.

02:20 So the black on the left does not appear on the right, and so it doesn't appear in the results. The orange on the right does not appear on the left, but that's fine because this is a right join, so it will appear on the right. And we just will have an empty result on the left. And then a full join is just like take everything.

02:39 And if there's a match, put them together. If there's not, just leave it null. So in our case, we want all the users. And if they have an image, we want the image as well. And what determines what's on the left and what's on the right is based on what you're selecting from. So we're selecting from the user. So that's going to be the thing that's on the left.

02:58 So with all that said, hopefully that gives you enough context to be able to actually implement a left join. There's instructions that you can follow. Have a good time with this one, and we'll see you when you're done.