1. Joining movie ratings with customer data
Until now, we have only discussed simple SQL queries involving retrieving data from a single table. Join queries allow us to combine - or join - data from multiple tables.
2. JOIN
You should know how to use JOINs from the pre-requisite course about joining data in SQL which covers many different JOIN statements. In this course, you will only need LEFT JOINs, which we will review now.
3. LEFT JOIN
The LEFT JOIN statement is one of the OUTER JOINS and is used to augment one table - that is the table on the left - with information from a second table - the table on the right.
We keep all rows from the left table and use only the rows from the right table which can be matched. This is also illustrated by the figure.
An identifier is needed to define which rows of the two tables can be matched.
4. Giving a table a name
Before we can start joining tables, let's see how we can use an alias to name a table. We will need this later for the join queries. In this simple query, we give the table customers the alias (or name) c. The word AS could be omitted, but we will always use it for better readability. To access the columns of this table we now use c-dot column name. In this example, this means that we look for the column customer_id only in the table c. This will be important when we have several tables with the same column names.
5. Tables for LEFT JOIN
So, here we have two tables which we want to join: renting_selected and customers_selected. We want to augment the left table with information from the right table, which means we want to add the customer information to each recorded movie rental in the table renting_selected.
In order to do so, we need to identify which table rows belong to each other. This matching can be done with customer_id.
So, how can we go about joining this data with an SQL query?
6. LEFT JOIN example
First, we select all columns from the table renting_selected and give the table the name r. Then comes the clause "LEFT JOIN" with the second table customers_selected which is named c.
Finally, after the word "ON" we have to specify under which condition the join can take place: that is when customer_id in the renting table matches customer_id in the customers table. It is also common to say we join ON customer_id.
Let's now take a look at the resulting table. The first three columns are identical to the table renting_selected. New columns are added from the table customers_selected.
In the table customers_selected, we have a record with customer_id equal to 3. Since customer_id 3 doesn't appear in the left table this record is not included in the result. customer_id 4 appears two times in the left table so the customer information of Julia Jung is matched with the renting table twice. Customer_id 5 appears only in the left table, therefore the columns for the customer data are filled with null values.
7. More than one JOIN
If we want to know which customer watched which movie and it is not enough for us to have just customer_id and movie_id, but we would also like to know the name of the customer and the title of the movie, then we have to join three tables to gather all this information.
In this query we augment the table renting first with the information form the table movies and then, with another LEFT JOIN, with the information from table customers.
In the SELECT clause we choose the title from the table movies with 'm-dot-title' and name form table customers with 'c-dot-name'.
8. Let's practice!
Now, it's your turn again.