Understanding Relationships & Joins

[00:00:00]
>> Now, we're gonna talk about relationships, and what happens when two tables love each other. So, so far, all of our queries have relatively been one to one, not relatively they've actually been one to one, right? Where, I write a query, and I get one row back for I look up something in the spreadsheet, and I get one row back from it.

[00:00:17]
That's kind of the methodology that we've been looking at, but now we're gonna start thinking about recipes, right? A recipe contains ingredients, so, you can think of something like a tomato, how many different recipes contain tomatoes, right? It's gonna be on pizza, it'll be on spaghetti, it'll be in a BLT, right?

[00:00:38]
So, one tomato, belongs to multiple recipes, right? But on the same direction, a recipe contains many ingredients, right? It's not that just one recipe is just a tomato, it's kind of a dumb recipe at that point, right? It has, many ingredients in it, so, a recipe has, many ingredients, ingredients belong to many recipes, this is called a many-to-many relationship, that make sense so far?

[00:01:11]
You can also have a one-to-one relationship, though typically you wouldn't model that as multiple tables, typically, you would model that as just one table, right? Where, a recipe has a, or let's say, an ingredient has a type, it only has one type, so, that's why we put it together in the same table.

[00:01:33]
But, in theory, those could be split in different tables, if that made sense to you. We are gonna talk now about a one-to-many relationship, which, let's think about a recipe, and a recipe has photos. You could have zero photos, you could have one photo, you could have 15 photos.

[00:01:52]
A photo is not going to belong to many recipes, that photo belongs to a single recipe, right? You can kinda think of this like an Instagram user, right? I don't post a picture, and then someone else owns it, right? So, there's no many to many relationship for photos, as a user, I post a photo, it is my photo, it only belongs to me.

[00:02:11]
We're gonna do the same kind of modeling for these recipes, that, you have a recipe, it has many photos, and it belongs to one recipe, this is a one, one recipe to many, many photos relationship. Well, how would you model this, if you were doing a spreadsheet? Let's say I had a spreadsheet, and I had ingredients in that spreadsheet, and I had, one recipe that had, 10 photos, and I had another recipe that had, 0 photos.

[00:02:44]
Well, the way that you could model that, in a spreadsheet, is you would have, 10 columns for photos. And then, the one that has 0 photos, just would have zero things in those columns. And then the one that has 10, has 10, the problem is now, every single row has this giant wasted space, right?

[00:03:04]
You have to have a column, for as many as one with the most wasteful, and it's confusing, to be honest with you. So, the way that we can do this, thinking again, in terms of recipe with this, we can have a first spreadsheet, that has just the recipes, and we can have a second table.

[00:03:23]
A second spreadsheet, that has just photos in it, and then we can use IDs to reference each other. So, that's what we're gonna do is, we're gonna have two tables, we're gonna have a recipe table, and we're going to have a recipe photos table, and then we're going to, join them, together.

[00:03:38]
Let's go ahead and start, a recipes table, nothing here is unique, or interesting to you except maybe that we're using a text, here for body, because bodies could be potentially very long, right? The recipes, so, it could be 1000 word essay in here, if we really wanted it to be, so, we're gonna use the unbound type of text, Okay?

[00:04:02]
I'm going to come over here to my database, and I'm just gonna paste that business, create table recipes, recipe ID, integer primary key, and a body, of text. Which could be kinda a dumb recipe, but, here we are, okay? I now have a table there, great, and, nothing here is really rocket science, let's go ahead and insert a few recipes into our little table here.

[00:04:43]
So, again, I'm just gonna copy and paste these, coz I don't wanna type all of that. Okay, so now I have *FROM recipes, you can see here I have, six recipes Pro tip, don't write a course while hungry, this is probably more for me, than for you but, I got really hungry in this course.

[00:05:16]
I think, literally the day that I wrote this be ended up ordering, dumplings coz I really wanted some xiao long bao [LAUGH]. And if you haven't tried any of these, especially any of these foods, you should definitely order those, okay. So, now we have a variable amount of photos, per recipe, right?

[00:05:40]
Imagine this is like all recipes, or something like that, right? Where, users can post photos of their recipes, right? And we wanna to associate with that, how are we gonna do that? We're gonna to create a second table, called recipes photos, again, this is gonna have a photo ID, which is gonna be a primary key, it's gonna have a recipe ID, which is just going to be an integer.

[00:06:07]
I know some of you are asking me right now, Brian, what about foreign keys? Just, hold on to it, we're gonna talk about in the next chapter, again we're gonna do it the naive way first, and then I'm going to show you how to do with foreign keys, okay?

[00:06:19]
Create table recipe photos, go ahead and do that for me, right now as well. Just to show you we have ingredients, we have recipes, and we have recipes photos. And then we have all of the IDs, which are great, they're all there, cool, and then now, we're gonna insert a bunch of stuff into recipes photos.

[00:06:52]
Okay? So, copy and paste, that, so, select star from recipes, photos You can see here, we have a bunch of photo IDs, and then recipe IDs, which obviously these are not unique, because again, one, which is a cookie, right? Can have multiple photos it does, it has five photos, he has something like jollof rice, which just has one.

[00:07:22]
You have something like ketchup curry that has five, and then you have something like xiao long bow which has nothing, right? It has no photos in here, that was ID six, so, let's say, I have shakshuka, and I wanna select all of the photos, for shakshuka. So, what I can do here is I can SELECT * FROM recipes_photos WHERE id = 4.

[00:07:51]
Sorry, WHERE recipe_id = 4. Cool, good, now, I was able to do that just because I happen to know off the top of my head, what the ID is for shakshuka, obviously, your code is not going to know that, right? You need to have some sort of way to query the first table, for the shakshuka, to grab its ID, and to use that ID, to query, the second table.

[00:08:21]
You could say that we need to join our data, you should say that, coz that's what we're gonna do. Let's go ahead, and do that, we're gonna do that, and we're gonna type that out so you can kind of see that happening in real time, so, let's write this query together.

[00:08:43]
We're gonna say, SELECT, and we want to get, the title, the body for all these recipes, and we also wanna get all of its photos, right? So, how are we gonna do that? We're going to wanna get the recipe title, so, I'm gonna say, recipe.title, recipe.body, and photo.url FROM, recipes_photos, recipes photos is the name of the table, right?

[00:09:25]
And, I'm gonna give it a nickname just for this query as photo, right? So, this corresponds to that, it's so I don't have to continually write out recipes and photos, I can write something shorter. And actually, after this, I'm just gonna do single-letter characters, coz it's just way faster, but, we're gonna be looking little bit explicit for this first one.

[00:09:46]
Okay? Then we're going to do an INNER JOIN Okay? We're gonna inner join recipes, recipe, so, notice the first one I have an s, the second one don't, again, I just chose the name recipe. We have to tell it what to join on, we're gonna tell it to join on, photo.recipe_id = recipe.recipe_id.

[00:10:21]
So, now you can see I have a title, I have the body, and I have the URL for all of these photos And pinata, all of these, right? So, that's what the join does, it allows us to reach into tables from two different tables, and get combined data back for both of them.

[00:10:42]
Because there's more in the photo table, that's why you get multiple copies of cookies, right? Because there are multiple cookies one, so, later in code we'd have to go back and collapse those down into one thing before we set it down on the API. And that's like, 90% for at least inner joins, I would say, most of the joins that I do, are this inner join.

[00:11:06]
We're literally about to talk about left join, right join, outer join, all of those kinds of fun things, but inner join basically says like there should be a one to one matching, right? If there is, a recipe with an ID, and in this one, there should be a corresponding one in the other table, and if it's lacking, don't include it.

[00:11:28]
Notice, there's no in here, xiao long bao in here, because, it does not have any photos, right? Therefore, it is left out of the results set, because there are no photos for it.