CODEWITHSUNDEEP
sql
ILovePizza
ILovePizza

Reputation: 191

sql - does join create a new table?

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?

Upvotes: 0

Views: 4460

Answers (2)

Yanji Lama
Yanji Lama

Reputation: 58

NO, it doesn't create the new table within the database but it only shows the output of the table. Yeah, it is just a kind of output For example I have two tables

Table_1

Table_2

my join query:

--Alias inner join
select t1.Table2_id, t1.[Name], t1.Class,
t1.Age, t2.Fee, t2.No_of_courses from Table_1 as t1
inner join Table_2 as t2  on  
t1.Table2_id=t2.id

Output

enter image description here

So it's just the temporary to show us the result of the join.

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 95062

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

Upvotes: 1

Related Questions