JOIN 2 Tables

Great! We've got the attention of a company that owns a small online store. They have 2 tables that are related to each other. The first table contains information about the products sold in the online store.

Here's what the product table looks like:

The second table contains product categories on the website, along with a brief description for each category.

Here's what the category table looks like:

We will work with these two tables.

We have the first task: join these 2 tables and see the amount of products in each category. To do this, we will use a JOIN statement.

Let's take a break from the main task and understand what a JOIN statement is and how to use it.

To join 2 tables, they must have a common column between them. Let's consider the usage of JOIN using the example of the employees and department tables. I'll remind you that their common column is employees.department and departments.name.

Note

Notice how I wrote the columns from these tables. First, I write the table name, then I put a dot and the column name. When we use more than one table in a query, we need to specify the table from which we are taking the column to make the code readable. Also, in the case where tables have columns with the same name, we need SQL to understand which table and column we are referring to.

Let's set ourselves the task of retrieving the total salary for employees in each department type (tech/non-tech).

The query to accomplish this task will look like this:


              1234
            
SELECT department.type, SUM(employees.salary) AS total_salary
FROM employees
JOIN department ON employees.department = department.name
GROUP BY department.type

Let's step by step understand what we did and how we used JOIN:

In the SELECT section, we specify the columns we need to retrieve from two tables, indicating the table name to which the column belongs;
In the JOIN section, we specify the table we want to join, followed by specifying the common column. In our case, it's employees.department and department.name;
Then, we aggregate the data by type (since we have an aggregate SUM() function in the SELECT section to find the total salary) and get the result we're interested in.

The query might look complex, so let's look at the general syntax for using JOIN:

This way, we can join tables and get the desired result, even if the information is scattered across multiple tables.

Note

Unlike the UNION clause, the JOIN statement allows us to fully combine tables, not just the common columns between them. It's also worth noting that when using the JOIN statement, we don't need to use subqueries with clauses, as we do with the UNION clause.

Now let's get back to the task at hand and reinforce our knowledge with practice:

Task

Swipe to show code editor

Your task is to join the two tables: category and product. The common columns for these two tables are product.category_id and category.id. Your task is to find the total amount of products in each category. To do this, you need to calculate the sum of the product.amount column. Use the alias total_amount for this column! At the end of your query, sort the result by the total_amount column in ascending order.

In the response, you should have 2 columns: category.name and total.amount.

Good luck!

Switch to desktop for real-world practiceContinue from where you are using one of the options below

Everything was clear?

Thanks for your feedback!

Section 3. Chapter 1

JOIN 2 Tables

Here's what the product table looks like:

The second table contains product categories on the website, along with a brief description for each category.

Here's what the category table looks like:

We will work with these two tables.

We have the first task: join these 2 tables and see the amount of products in each category. To do this, we will use a JOIN statement.

Let's take a break from the main task and understand what a JOIN statement is and how to use it.

Note

Notice how I wrote the columns from these tables. First, I write the table name, then I put a dot and the column name. When we use more than one table in a query, we need to specify the table from which we are taking the column to make the code readable. Also, in the case where tables have columns with the same name, we need SQL to understand which table and column we are referring to.

Let's set ourselves the task of retrieving the total salary for employees in each department type (tech/non-tech).

The query to accomplish this task will look like this:


              1234
            
SELECT department.type, SUM(employees.salary) AS total_salary
FROM employees
JOIN department ON employees.department = department.name
GROUP BY department.type

Let's step by step understand what we did and how we used JOIN:

In the SELECT section, we specify the columns we need to retrieve from two tables, indicating the table name to which the column belongs;
In the JOIN section, we specify the table we want to join, followed by specifying the common column. In our case, it's employees.department and department.name;
Then, we aggregate the data by type (since we have an aggregate SUM() function in the SELECT section to find the total salary) and get the result we're interested in.

The query might look complex, so let's look at the general syntax for using JOIN:

This way, we can join tables and get the desired result, even if the information is scattered across multiple tables.

Note

Unlike the UNION clause, the JOIN statement allows us to fully combine tables, not just the common columns between them. It's also worth noting that when using the JOIN statement, we don't need to use subqueries with clauses, as we do with the UNION clause.

Now let's get back to the task at hand and reinforce our knowledge with practice:

Task

Swipe to show code editor

In the response, you should have 2 columns: category.name and total.amount.

Good luck!

Switch to desktop for real-world practiceContinue from where you are using one of the options below

Everything was clear?

Thanks for your feedback!

Section 3. Chapter 1

Switch to desktop for real-world practiceContinue from where you are using one of the options below

Intermediate SQL

JOIN 2 Tables

JOIN 2 Tables