Introduction to Joins
Relational Databases and Data Normalization
In this topic we are going to develop a database to keep information about
movies. Along the way we will need to track information about the movie that
isn't singular information. With a single table we store information in columns
that is singular. We also want to avoid repeating information. For instance, the
movie's title is a single piece of information while the list of actors in the
cast are multiple pieces of information. Similarly, the rating (G, PG, etc)
would be repeated information per row. We will learn how to create relations
to store this information in separate tables and join / relate it back to
the movies.
Creating our database
This is the database we will use for the rest of this discussion.
Primary Keys
Since we will be relating information between multiple tables we need a way to
uniquely identify a row of data in a table. This is known as the table's
PRIMARY KEY and is a special attribute of the column that says that it
uniquely identifies the row and also cannot repeat.
You might have an existing column that you feel uniquely identify the row. For instance, you might think that the movie's title would uniquely identify the movie. However, we know that sometimes a movie's title changes during the production, or even uses the name of a movie that has existed in the past. So if we used the title to uniquely identify it we would run into many issues.
Fortunately databases provide their own way of supplying a unique value for each
row in the database. We call this the SERIAL column.
So when creating our Movie table we will use this schema:
We now have a column Id that the database will ensure is unique and identifies
the row (PRIMARY KEY) and is automatically generated sequentially by the
database (SERIAL)
Let's add some movies to our database:
Foreign Keys
In order to keep track of the rating for any given movie we will add a single
table, named Ratings that will store the name of the rating. Since we also
want to uniquely identify the ratings, we'll ensure this table also has a serial
primary key.
Let's insert some ratings:
Let's also add a table to keep information about our actors. For this table we
want to know the full name of the actor and their birthday. We'll also create an
id that is a PRIMARY KEY and is SERIAL
Let's also insert some actors
So now we have a way to identify each movie, rating, and actor. Next we will talk about how to relate the three sets of information together.
One to Many
When we have a relationship such as the ratings for a movie, we say that this is a "One to Many" relationship. That is, a movie has one rating (e.g. the movie Bambi is rated G), but a rating applies to many movies (e.g. there are many movies with a G rating)
When describing our database it is often useful to have a visualization of the
structure. These diagrams are called Entity Relationship Diagrams, or ERDs
The ERD of our movies and ratings looks like the following. (NOTE We'll add in the actors soon...)
Let's add a new column to our Movies to indicate WHICH rating is associated
to each row representing a movie.
The column we are adding is a RatingId that is an integer since this is the
same data type as a SERIAL which we are going to relate to. The NULL
indicates that we are allowed to have no value and the value to place in the
column when no data is present is NULL. Next we indicate that this is a
foreign key (we are relating this table) to the Ratings table. We also
specify the column in the other table, in this case Id in Ratings, we mean
to match.
Now our ERD looks like this. The RatingId from Movies links (joins) us
to the Ratings table
Now we can specify the RatingId associated to each movie when we insert the
movie.
Joining tables
So now that we have these two tables, how do we join them together so that we can retrieve information about movies and their ratings or get information about a rating and the associated movies.
Query movies and also get their rating
This query will give us movies and their ratings, but only for movies that have
a RatingId that matches an Id from the ratings table. That is, any movie
with a null value for RatingId (or a value that doesn't match an id) will
not be in the results.
This default kind of join is called an INNER JOIN and can be seen in the
diagram below
See this blog post for a more detailed description of the various joins.
If we want to see ALL the movies and include the ratings table information
when there is a match we modify the query to use LEFT JOIN
Query for all movies that are "R", adding a WHERE clause
We can still add a WHERE clause when using a JOIN but now we specify the
table and column we want to filter by. We do this since we have more than one
table involved.
Query all the movies and include ratings when possible
The left join indicates that we want everything from the left table, in this
case movies, and matches from the ratings table. For movies that have no
matching ratings row the information will be null
Many to Many
For the list of actors in the cast we might say "A movie has many actors" but also "An actor has appeared in many movies." In this case we need to relate these two tables in a different way.
The ERD of this looks like:
In the case of a many-to-many relationship we cannot place the foreign keys on either of the tables. In this case we need a third table, commonly referred to as a join table to store the relationships. In this table, we will place two foreign keys, one to the left (movies) and the other to the right (actors). We attempt to name this table based on the relationship between the two tables.
In this case we are trying to represent the relationship between a movie and the
actors. We could call this relationship Roles.
Create cast membership for Orlando Bloom in Pirates & LOTR. The three LOTR
movies are id 7, 8, and 9. The Pirates movie is id 2. Orlando Bloom's id is
1
NOTE: In the queries below, -- is a SQL comments (like a JavaScript //)
Create cast membership for Warwick Davis in Harry Potter and Hitchhikers. Harry
Potter's movie id is 3, and Warwick's actor id is 2
Create cast membership for Martin Freeman (actor id is 3) in the Hobbit
(movie ids are 4, 5, and 6) & Hitchhikers (movie id 10)
Query for the casts and actors
In order to tie Movies to Actors we need to join the movies first to the
Roles and then join the Roles to the actors. Since the Roles table has
relations to each of these tables, it acts as the link in the chain between the
two tables.
Notice for the actors entry Orlando Bloom there are multiple entries in
roles since he has appeared in several of our movies.
Adding information to the join table.
What if we wanted to capture the name of the character the actor played? Where
would we put that attribute (column). It can't go on the Movies table since it
isn't distinct to a movie. It can't go on the Actors table since it isn't
unique to that either. The correct place here is to place that column on the
Roles table.
Let's call this new column CharacterName and add it to the Roles table.
Now that we have done that, we can add in a few character names. In order to
know what rows to update, lets add the Roles.Id to our query above.
Now lets update the roles for all of our actors
Let's see what our query looks like now: