So now that we've spread our data out across all these tables, created primary keys, and linked them together with foreign keys, it is time to actually make some sense of it. And this is the power of relational databases. Again, I've only showed you like a little of seven records. You have to imagine millions, instead of seven. So now we're going to take these foreign keys and we're going to traverse the foreign keys to walk across this web of information that's now efficiently stored. The SQL construct that we use for this is called JOIN. The JOIN operation links across several tables as part of a SELECT. So it's an extended SELECT that extends across a number of tables. And you have to tell JOIN how to connect the tables and that's what's called the ON clause. So we have all this data and these are just the tables that we just got done creating. And so we have the id, and we have the foreign key, and so we're going to connect them together. So we're going to get a SELECT. We want to produce an output that looks like this. We don't want the numbers any more because that's not good for users. Those foreign keys or primary keys are just bookkeeping stuff we do as database creators, but we want to make it so it looks nice. So now we're going to have the SELECT. We have a slightly different format now. We have the table name followed by the field within table album.title, artist.name. So we're now selecting data from two tables, and so we say, oh well, FROM FROM. You can do this in either order. So we're going to have two tables, the FROM the album table joined with the artist table. So we're horizontally connecting the album and the artist table. And I follow the arrows. So I start in the album table and then I'm kind of like looking these guys up, I'm saying,oh, okay, 2 and 1, I want to turn those into the strings. So you could do it either way, but I tend to go FROM and I tend to follow it. So album joined with artist. Then we've connected these two tables, but then we have to have this ON clause to say when rows are connected and we want the 1 to connect to 1 and 2 to connect to 2. That's important because that's meaning we in effect look up the corresponding string of name, given the foreign key 1 or 2. Right? And so that's what the ON album.artist_id equals. That's the album artist_id field, and equals artist.id. Now again, when you have a convention, I can look at this and I can note, okay, that's a table named album, artist_id is a foreign key into the artist table, artist.id is the primary key. And so I can see all that stuff. And so it goes through and it looks at all these things and it connects those and shows us only the data. So one of the things that the SELECT, I didn't make too much of a fuss about it, SELECT picks among the things that you could see. It shows you what you want to see. We don't want to see the id, we don't want to see artist_id, or id from artist, but we could see all that, okay? So SELECT picks what we want to see. So that's basically how we can say, I'd like to see a little bit more. I want to see the album title, the album's artist_id, the artist's id. So we're going to explore and show the data that is the part of the ON clause. Now, in the previous one we just didn't show it. So if we just add these and the rest of those, it's just adding those things compared to the last one. And basically, you just get these two columns where artist_id and id and then it just shows you how the ON clause has made that connection for you. Now, that's what's called a INNER JOIN. So the INNER JOIN is filters where they match, right? But in a sense, the JOIN is taking and looking at all possible combinations of these things. Now, in a table where there's only two and two, the number of possible combinations is four. All the rows of the first table combined with all the rows of the second table, that is four. Now, you can actually express a JOIN, and it's called a CROSS JOIN. So the INNER JOIN means take the things that match. CROSS JOIN means join everything. And you'll notice that this CROSS JOIN doesn't have an ON clause because it doesn't need an ON clause. So track CROSS JOIN with genre, says take all the combinations. And in this case, the track has four items in it, and the genre has two items in it. So we end up with eight rows when we come back. And we're also seeing the genre's id and the genre.id which is the thing we're eventually going to use as the ON clause, but you'll notice in the CROSS JOIN, in particular the CROSS JOIN, we both get the ones that match, and we get the ones that don't match. The difference between the INNER JOIN is these ones that don't match, 2, 1, and these get chopped out when we're doing the INNER JOIN, but the CROSS JOIN shows us all those. So the CROSS JOIN is like the INNER JOIN with a WHERE clause. You can almost think of the ON clause as a WHERE clause after you've done a CROSS JOIN. Normally, we don't want to do this, but sometimes we do want all combinations. This is not a very efficient thing. Imagine a million on one side and a million on the other side. You don't really want that. You just want the ON clause and the connection. I'm just showing you this, not because I expected to do CROSS JOINs, but just so you kind of get the basic mechanics of what JOIN is doing. It's like taking combinations and then filtering. The whole JOIN is these eight rows, right? And the ON clause throws away the rows where these two fields don't match. And in some databases, you do this not with a JOIN and an ON clause, but you just say, from this, comma, that, comma, that, and put it on the WHERE clause. I don't think that's as pretty, but you might. And that's okay. When we're going to do this with the INNER JOIN, which makes a lot more sense, right? Now we're going to do an INNER JOIN. If you don't say INNER, it's an INNER JOIN, which means it's filtering. You take all the tracks, and you look up and you put the corresponding genre in. And we're only asking for track title and genre name. So this is a normal JOIN, INNER JOIN, with an ON clause. And all I'm showing here is, this JOIN will reconstruct that vertical replication, right? The JOIN will reconstruct the vertical replication. The Rock, Rock, Metal, Metal. So that was the thing we didn't like, but that's the thing we need for the user interface. And this gets complex, but again when I write this, I write it really fast because all my JOINs look the same. All my field names look the same, all my patterns look the same. So I'm going to say, I want to see the track title, the artist name, the album title, and genre name from the track joined with the genre, and here's the matching condition for that JOIN. Joined with the album, here's the matching condition for that JOIN. Joined with the artist, and here's the matching condition for that JOIN. Again, you can kind of see how I'm just going to copy and paste, change a little bit. And it's not that all bad. And we're only seeing the text things. And now, what you see is you see the title of the track, you see the name of the album, you see the name of the artist, you see the title of the album, and then you see the genre. And you see all that vertical replication back. Again, I like to think of this as with all of this, we compressed the database using numbers rather than strings and JOIN reconstitutes the strings, but it's not stored anywhere. It's just sort of at the last moment, we make the strings and we show them to the user. And then it's really efficient still sitting in the database. And so that was a long set of data models, and serial columns, and IDs, and foreign keys, and primary keys, and all that stuff just to go from the point where we had a prototype UI that had vertical replication in it, to a database that had no string replication in it, back to a UI that we can then reconstruct on the fly using all of this JOIN stuff. Now the one thing I want to touch on is this ON DELETE CASCADE which I put in all those CREATE statements. Now that we've done this, we can see how this ON DELETE CASCADE. So you can think of a many-to-one relationship, meaning many tracks go to one genre, or you can think of the genre as kind of a parent row. And the question would be, what if we removed that row? What would we do with these? Because these point now to a row that doesn't exist. And so this is where the ON DELETE CASCADE is helping us maintain these internal links. I told you that these are just kind of integers, but when we have a constraint foreign key, we tell it that oh, I know what, when this parent row gets deleted, what to do with the corresponding rows in this child field. Okay? So when we say ON DELETE CASCADE, that means cascade delete from the parent into the child. So once you delete this row, these rows are going to be gone too, okay? So if you run this command, that is going to not just delete one row from the genre, but it's also going to delete two rows from the track, right? We have four rows. We delete one row from genre and then we've inadvertently as a side effect, we have deleted two rows. The ones that had a genre id of 2, we've deleted those rows from the track as well. So that's how the ON DELETE CASCADE. Now, there's more choices that you have. You can do RESTRICT, meaning that that delete of Metal wouldn't work. Meaning that if you delete Metal from genre, then there's going to be these rows in track that don't point. And then that would be a failure. It would blow up on us. Remember that when SQL blows up, it's often because you or I asked it to enforce a rule. So if I said ON DELETE RESTRICT, that means don't let me delete things if I would break my internal data model. And you like that. It may seem wrong to you, like how come I can't do what I want to do. Well, then tell it you want to do that. CASCADE is the one I tend to do because it keeps your data model clean. So if you delete a parent row, you throw away the child row so the consistency is maintained. And then the last thing you do is you can set it to null, which effectively deletes not the whole row, but it deletes those foreign key columns. So you don't end up with the 2, you end up with null in that. Meaning it doesn't point. Now, if you're going to do DELETE SET NULL, you've got to allow your foreign key to have a value of null, because you can decide whether or not is this an integer field or integer null. Meaning integer null field means I'm allowing nulls in this field, which null is empty. So I'd have to say INTEGER NULL, if I was going to say ON DELETE SET NULL. And it wouldn't even create the table that way. Again, you always think, like, oh, it won't create the table, that's so mean. It's like no, just put DELETE CASCADE. I don't know. It's a weird thing that you've got to get used to when you're building databases where you're like, I have decided to make you enforce rules on me the programmer, because it saves you all the time. So that's your choices for ON DELETE. So the next thing we're going to take a look at is many-to-many relationships. And at this point, you're probably thinking, well, artists and albums, that's not quite right. Yeah, because they're not really one-to-many relationships in the real world. So now we're going to get to an example that shows you the other really valuable way of representing data called the many-to-many relationship.
