1. Using joins
Welcome to Chapter 3 which covers combining data.
2. Chinook dataset
The dataset we've been using is made up of several tables. Here in this ER diagram, you can see that there are several tables from playlists to employees.
Some of these tables share common columns. For example, the invoice table has a customerid field that is also in the customer table.
3. Combining data from more than one table
Up until now, we've been running queries within one table. What if we wanted to run queries on more than one table?
For example, say we wanted to output all the albums of an artist. Although album has artistid, we want to use the artist's name because an id is not interpretable. This means we would need to combine rows from the album and artist table.
4. SQL joins
In SQL, we call combining data from different tables, joining tables.
As we will see in this chapter, there are several types of joins. We will begin with the simplest - the inner join.
5. Inner join
Back to our artist albums question! We can use an inner join to get a table with the albums and their associated artist names.
6. Breakdown of an inner join
Let's break down the inner join syntax.
We first select the columns we want to output and indicate which tables the columns come from. If the column names are unique to a table, you don't need to do this, but this is best practice because it makes your code more interpretable.
Then we use the FROM clause to indicate the relevant tables and separate them with the INNER JOIN clause.
Finally, we use the ON clause to define which columns we are joining on.
After that, you can add other SQL clauses.
7. Inner join
We can query the resulting joined table with the WHERE clause for specific artists. This way we don't need to find or memorize an artist's id.
8. USING instead of ON
You can use the USING clause instead of ON if the defining JOIN column is identical for both tables. In our case, it works because both the album and artist table named their column ArtistID. The column name needs to be enclosed in parentheses after the USING clause.
9. Table aliases
In an earlier video, we covered column aliases. We can also alias tables. Table aliases are very useful when doing joins. For example, this query outputs a customer's name and their corresponding support rep's name.
We can improve the readability of this query by aliasing customer to c and employee to e after the FROM clause.
10. Joining more than two tables
We can join more than two tables. For example, let's say along with the album name, we wanted to get the track names of the artist.
We would need to join these three tables using the albumid and artistid columns.
This is what the query would look like to do this.
The FROM clause is only used once. After the first join, there is another join that combines the third table.
11. Joining more than two tables
And here's what the output looks like!
You can join as many tables as you would like - but beware of long queries.
12. Let's practice!
Let's get to some exercises.