Find Intersecting Data with Postgres’ Inner Join
You have a table of movies and a table of directors; how can you see which director created each movie? An inner join will link related records so that you can answer questions like these. Inner joins are the most commonly used SQL join, because they only return data that is common in both tables.
[00:00] We have a movie rental store with rentable movies and rentings. We want to see all of our renting information in one place. We're going to select * from RentableMovies. We're going to join that to the movies table, so we can see these two things together in action.
[00:16] We use an "on" statement to say which of these fields is the same, so that the ID column in the movies table is supposed to be equal to the RentableMovies' movie_ID column.
[00:27] We can see what this looks like. It looks like you just smashed the RentableMovies table, which we'll remember is this movie_ID, store_ID, and copy_number -- that's what's over here -- in with the movies table. The movies table has its ID, title, release_date, count_stars, director_ID. You just smooched those two things together.
[00:50] Pretty straightforward, but we want to ultimately get to the Rentings, so let's get some store information here. The stores, and we'll say on store.ID = RentableMovies.store_ID column, but we see here it doesn't matter how many inner joins we make. We can just keep smooching them onto the end here.
[01:18] We start getting our IDs mixed up. We see that stuff is conflicting. Let's just take the information that we want. Let's take all of the RentableMovies columns, all of these guys here. Then we'll say maybe we'll get movies.title and we'll get the stores' location. We have that information here.
[01:45] This is the title that was rented. This is the location it was rented in, so the copy number of that. If we remember, the Rentings table is a little bit more complicated. Rentings has a primary key that is built up of the movie_ID, the store_ID, and the copy_number, which is the same as this information. We're going to have to make a compound inner join here.
[02:13] Let's say Rentings.movie_ID = RentableMovies.movie_ID. Copy this. The store_IDs are equal and copy numbers are the same. This will give us all of the rentings for each of these individual rentable movies and there could be multiples here, so I just wrote this wrong.
[02:48] What an "inner join" does is it only finds intersecting data. There were only actually two rentings in our rentings table, which is why some rentable movies that were never rented didn't get sent back to us. We didn't return anything about that.
[03:04] Let's also give us all the rentings information. We have the guest_ID, all this information. I don't think we really want all that, so let's just take due_back, let's take rentings.returned. We've got that.
[03:20] We also probably want to know some information about the guest. Let's "inner join" into the guest table, "on guest.ID = rentings.guest_ID." Let's take guest.Name and guest.email, so we know who has the movie.
[03:40] This is really cool. We've built up this custom view that gives us all of the information about the rentings. At this point, we don't even need all this information for RentableMovies.
[03:52] Let's rerun this. This is the information we want. We know that I've got a copy of "Kill Bill" and that it's due back on some date in 2017 and that I haven't returned it.
[04:05] We know, apparently, this is a famous video store because Bradley Cooper here, too. He has actually returned a copy of Kill Bill. It does matter for us to get a RentableMovies.copy_number because these are two different movies here. Now we have the copy_number as well.