Notice: This page requires JavaScript to function properly.
Please enable JavaScript in your browser settings or update your browser.
JOIN 2 Tables | Joining Tables
Intermediate SQL
course content

Course Content

Intermediate SQL

Intermediate SQL

1. Grouping
2. Nested Subqueries
3. Joining Tables
4. DDL and DML in SQL

bookJOIN 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
copy

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

  1. In the SELECT section, we specify the columns we need to retrieve from two tables, indicating the table name to which the column belongs;
  2. 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;
  3. 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

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 desktopSwitch to desktop for real-world practiceContinue from where you are using one of the options below
Everything was clear?

How can we improve it?

Thanks for your feedback!

Section 3. Chapter 1
toggle bottom row

bookJOIN 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
copy

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

  1. In the SELECT section, we specify the columns we need to retrieve from two tables, indicating the table name to which the column belongs;
  2. 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;
  3. 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

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 desktopSwitch to desktop for real-world practiceContinue from where you are using one of the options below
Everything was clear?

How can we improve it?

Thanks for your feedback!

Section 3. Chapter 1
toggle bottom row

bookJOIN 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
copy

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

  1. In the SELECT section, we specify the columns we need to retrieve from two tables, indicating the table name to which the column belongs;
  2. 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;
  3. 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

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 desktopSwitch to desktop for real-world practiceContinue from where you are using one of the options below
Everything was clear?

How can we improve it?

Thanks for your feedback!

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
copy

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

  1. In the SELECT section, we specify the columns we need to retrieve from two tables, indicating the table name to which the column belongs;
  2. 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;
  3. 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

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 desktopSwitch to desktop for real-world practiceContinue from where you are using one of the options below
Section 3. Chapter 1
Switch to desktopSwitch to desktop for real-world practiceContinue from where you are using one of the options below
some-alt