We’ve learned many things so far and covered most of the basics of SQL. But until now we’ve only been working on one table at a time. During this tutorial, we will learn how to join tables to provide an upgrade for data analytics!
📌JOIN: select and combine columns that have matching values in 2 tables
#Table of Contents
Query 1. #INNER JOIN
SELECT country.country_id, country.country, city.city_id, city.city FROM country INNER JOIN city ON country.country_id=city.country_id;
Query 2. #INNER JOIN with 3 tables
SELECT film.film_id, film.title, film_category.category_id, category.name FROM film INNER JOIN film_category ON film.film_id = film_category.film_id INNER JOIN category ON film_category.category_id = category.category_id ORDER BY film_id ASC;
Q1. Let’s combine the country table and city table by country_id!
(check the data in country_id, country, city_id, city)
SELECT (column name/the table you want to see that column from
‘table.column’) FROM (table1) INNER JOIN (table2) ON (table1.column name) = (table2.column name)
First, we need to understand the concept of joining the country and city tables. If we look at the country table, we can see the columns country ID, country name, and last update. The city table has the columns city ID, city name, country ID, and another last update.
As you can see, both tables have the column country_id in common. They also have last_update in common but while the name is the same, the information differs. This information only refers to when the data in each column was created or changed. So be aware that while the column names are identical, the data is different in both tables and we can not use the information in these columns to combine the tables. We’re only going to focus on country_id.
If you need to check the desired data of two tables at once without switching back and forth between them, then
JOIN is the query to use. There are several types of
JOIN queries. For our basic SQL series, we will first cover
INNER JOIN and later on,
OUTER (LEFT/RIGHT) JOIN.
INNER JOIN” that we’re going to cover today basically combines tables based on common data, and creates a new table with the combined data.
Alright, let’s try to make this table. First, write the name of the column you want to see after
SELECT. We’re going to do this a little differently this time. Before when we used only one table, we wrote our query like this:
But now that we’re using two different tables that have the same column name, we need to identify which table we want information from:
We can distinguish between the two by labeling them first. For the country table, we should write it as
country.country_id, country.country and the city table would be
FROM query is a little different too. We need to state specifically which table to search first, then add in that we want to combine (
JOIN) the second table. Like this:
FROM country INNER JOIN city;
💡 If you write
JOIN instead of
INNER JOIN, you will get the same result. But because we will learn
OUTER JOIN later, we want to make a clear distinction here that there is a difference between the two. So let’s stick to
We need one more step before we run our query! Using the
ON query, we’re going to specify the tables and the column name they have in common. We mentioned before that the only column they have in common is country_id, so let’s use that in our query like this:
ON country.country_id = city.country_id;
Your final query will look like this:
SELECT country.country_id, country.country, city.city_id, city.city FROM country INNER JOIN city ON country.country_id = city.country_id;
This query looks a little… busy… doesn’t it? And long! We can clean up our query easily by renaming our tables into something more manageable. We’re going to use alphabet A and B for this example. Instead of using country for the country table we will just use
a, and for the city table we will use
SELECT a.country_id, a.country, b.city_id, b.city FROM country a INNER JOIN city b ON a.country_id = b.country_id;
Q2. Let’s combine 3 tables by film_id and category_id and sort the results by film_id! (check the data in file_id, title, category_id, name)
SELECT (column name/the table you want to see that column from
‘table.column’) FROM (table1) INNER JOIN (table2) ON (table1.column name) = (table2.column name) INNER JOIN (table3) ON (table2.column name) = (table3.column name)
Next let’s try using a common column to combine three tables. This time we’re going to use the tables film, film_category, and category.
The basic structure here is the same as query 1. But since there are many tables and columns, let’s break them down one by one.
First, write down the table and column name of the data you want to see after
SELECT. The table name always comes first, followed by a period (
SELECT film.film_id, film.title, film_category.category_id, category.name
Then we need to write the first table name (film) after
FROM and indicate with
INNER JOIN that we want to combine the second table (film_category). To add the 3rd table, just add another
INNER JOIN followed by the third table name (category)!
FROM film INNER JOIN film_category INNER JOIN category
But wait!! ⛔️ Where do we plug in
ON? We can’t combine the
ON query at the end, since we’re calling on data from two different locations. So separate them by placing two different
ON queries after their respective
INNER JOIN film_category ON film.film_id=film_category.film_id
INNER JOIN category ON film_category.category_id=category.category_id
🔑 You can also add in some organization by using
ORDER BY file_id ASC
Your final query should look like this:
SELECT film.film_id, film.title, film_category.category_id, category.name FROM film INNER JOIN film_category ON film.film_id=film_category.film_id INNER JOIN category ON film_category.category_id=category.category_id ORDER BY film_id ASC;
⭐️ Let’s try using alias here as well. We can clarify the names after
FROM, and use them with
SELECT a.film_id, a.title, b.category_id, c.name FROM film a INNER JOIN film_category b ON a.film_id=b.film_id INNER JOIN category c ON b.category_id=c.category_id ORDER BY a.film_id ASC;
📰Combine the country, city, and address tables using the country_id and city_id columns, then filter the information to only see addresses in South Korea!
This example sentence is similar to the one we provided in the other tutorial, where we ran three different queries to find out information about cities in South Korea. This was before
INNER JOIN, so we had to look up the results in 3 separate instances. But let’s use what we learned today to view the results all at the same time.
Your final query should look like this!
SELECT country.country_id, country.country, city.city_id, city.city, address.district, address.address FROM country INNER JOIN city ON country.country_id=city.country_id INNER JOIN address ON city.city_id=address.city_id WHERE country='South Korea';