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;

#JOIN

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 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.

country table and city table

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.

The “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.

country table and city table combined

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:

SELECT country_id;

But now that we’re using two different tables with the same column name, we need to identify which table we want information from:

SELECT country.country_id;

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 city.city_id, city.city.

❗️ The 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 INNER JOIN.

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. 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 the country for the country table, we will use a, and for the city table, we will use b.

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.

combing 3 tables into 1~

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

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, 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 call on data from two different locations. So separate them by placing two different ON queries after their respective INNER JOINs.

INNER JOIN film_category ON film.film_id=film_category.film_id
and
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.

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;

#Practice Time

📰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.

combine 3 tables into one!!

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';