[SQL Basic] Let’s find out the movie genres by joining tables! — SQL INNER JOIN

#13. Marketing/Data Analysis for Beginners

#Glossary:

📌Lesson 3: SELECT / FROM / WHERE
📌Lesson 4: ORDER BY
📌Lesson 5: AND / OR / IN
📌Lesson 6: LIKE
📌Lesson 7: AS /DISTINCT / IS NULL
📌Lesson 9: COUNT / SUM
📌Lesson 10: AVG / MAX / MIN
📌Lesson 11: GROUP BY / HAVING
📌Lesson 12: CASE
📌Lesson 13: INNER JOIN

🆕 🌟
📌JOIN: select and combine columns that have matching values in 2 tables

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!

#Table of Contents

Query 1. Let’s combine the country table and city table by country_id!(check the data in the columns country_id, country, city_id, city)

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. Let’s combine 3 tables by field_id and category_id and sort the results by film_id! (check the data in file_id, title, category_id, name)

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

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.

Alright, let’s try to make this table. First, write down 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 that have the same column name, we need to identify which table we want information from:

SELECT country.country_id

Let’s 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;

But this looks a little… busy… doesn’t it? 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 b.

⭐️ We learned how to use AS to rename columns in tutorial 7. Now let’s go over how to do this for tables. We can set alias by just writing the new name after the old one, which we clarify in the FROM query. There are 3 ways to do this in SQLGate:

1️⃣ FROM country AS a

2️⃣ FROM country a

3️⃣ FROM country “a”

⛔️ Before we can use this alias, we need to turn the shortcut on in SQLGate. You can do this by going to Tools > Options > SQLEditor > Options (Editor Options) > Autocomplete > under the SQLAutoComplete menu > From > Add alias > abc.. — If you need visual guidance, check out our video at the end of this tutorial!

Good job! Now although we clarify it with FROM, we can use first with SELECT to make our query easier and cleaner. We’re going to use 2️⃣ in this example. Check out the picture below to see the cleaner version of our query!

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 that we want a combination with INNER JOIN followed by the name of 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 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 in the FROM query, and use them in the SELECT query.

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;

🔑 Don’t forget to specify a in the ORDER BY query as well!

#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 6th 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.

🔑 Start with identifying the table names and columns you want to see. If you want to check which columns the tables have in common, take a look by running SELECT * FROM(table name); — Remember our three table names are country, city, and address.

🔑 Your query should begin like this:

SELECT country.country_id, country.country, city.city_id, city.city, address.district, address.address

Next, we want to combine them all together using FROM for the first table and adding INNER JOIN for the next two.

👍 Take a moment to try on your own!

🔑 Remember to put the table name first, followed by the column you want to combine. We need to remember to distinguish between the two columns with the same name! You can do this by writing the table name first, a period (.) and then the column name.

FROM country
INNER JOIN city ON country.country_id=city.country_id
INNER JOIN address ON city.city_id=address.city_id

🔑 Before we put it all together, we need to add a filter so that we only see the information for South Korea. We can do this by using the WHERE query!

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

🔑 If you want to use alias here, it’s easy to do! Just remember to specify it in the FROM query like this:

FROM country a
INNER JOIN city b
INNER JOIN address c

1️⃣ And you can use it in SELECT like this:

SELECT a. country_id, a.country,
b.city_id, b.city,
c.district, c.address

2️⃣ With the ON query, you can just write it as a = b. Like this:

ON a.country_id=b.country_id
ON b.city_id=c.city_id

3️⃣ Don’t forget to clarify in the WHERE query that you want it be organized by the South Korea present in the country ( a ) table.

WHERE a.country='South Korea';

4️⃣ Put it all together like this:

SELECT a.country_id, a.country, b.city_id, b.city, c.district, c.address FROM country a INNER JOIN city b ON a.country_id=b.country_id INNER JOIN address c ON b.city_id=c.city_id WHERE a.country='South Korea';

Good job!! 🙌 Check out the video tutorial for a more visual guide on how to use an alias for your tables! 😄

# Tutorial Video

# [SQL Basic Series] — Marketing/Data Analysis for Beginners

1. What is SQL? — Database Introduction
2. Downloading SQLGate and Connecting to a Database
3. What kind of data is in the ‘film’ table? — SELECT/FROM/WHERE
4. How to sort film lists by price (rental rate)? — ORDER BY
5. Can I rent a 3 hour movie for $0.99? — AND/OR/IN
6. How can I find a movie that’s thrilling? — LIKE
7. How to rename columns and see unique values? — AS/DISTINCT/IS NULL
8. Let’s see Sample Database tables!
9. What is the total length of all the films added together? — COUNT/SUM
10. What is the average film length? — AVG/MIN/MAX
11. How many movies have the same rating? — GROUP BY/HAVING
12. Let’s designate a value based on the rental duration. — CASE
13. Let’s find out the movie genres by joining tables! — INNER JOIN
14. Let’s check movie rentals by the inventory list! — LEFT/RIGHT JOIN
15. Let’s find customers that have rented movies priced $9.99! — Subquery
16. How to work with Date Functions in SQL — DATE_FORMAT, DATEDIFF
17. How to work with String Functions in SQL — CONCAT, LENGTH, SUBSTR
18. Let’s use some Numeric SQL functions — TRUNCATE, ROUND, MOD
19. Let’s learn some advanced CASE queries!
20. Let’s learn some advanced JOIN queries!