sql – does join create a new table?

January 11, 2022

when I use join in mysql (or sql) does it create a new table?
Or does it create a virtual table like the view command?

>Solution :

Each query result is a table. It consists of columns and rows and can be treated like any other table in SQL. E.g.:

select *
from departments
join
(
  select department_id, count(*) as number_of_employees
  from employees
  group by department_id
) department_info using (department_id);

Here we create a table we call department_info in our query and join this table to the existing departments table. This creates another table now consisting of departments plus the number of employees in it. This is the query result we show.

It is a query (select ...) which creates a table. The join is just a part of it.

These tables, however, are only temporary. While departments and employees in above example are stored tables, department_info and the final query result are not. They don’t get stored. If you want to store a query’s result table, use CREATE TABLE AS. E.g.:

create table department_employees as
select *
from departments
join
(
  select department_id, count(*) as number_of_employees
  from employees
  group by department_id
) department_info using (department_id);