Temporary Table in MySQL

A temporary table in MySQL is a table that allows one to store temporary result set of a query, and which one can reuse multiple times during one session. A temporary table is useful in cases where a SELECT statement is expensive to query the data as it may involve complex and multiple joins on tables such that every table contain huge amount of data. So, one can use the temporary table to store the result and then use another query to process this data.

How is Temporary Table Different from MySQL View?

Once you create a temporary table, it will exist in the database until the current session is not terminated. So, when you close/terminate your session, it is automatically dropped. Now if you start another session you will not see the temporary table of your previous session. This means, two separate sessions can have temporary tables with same name without having any name conflict. A view once created is persistent across multiple sessions and the data in the view is maintained even if your current session is terminated.

1. How to Create Temporary Table?

The syntax to create a temporary table is as very similar to syntax for creating any normal table. Instead of CREATE TABLE which creates a normal table, we add TEMPORARY keyword so, eventually we use CREATE TEMPORARY TABLE clause to create a temporary table.

Syntax:

CREATE TEMPORARY TABLE <table_name>(

<column1> <datatype>,

<column2> <datatype>,

<column3> <datatype>,

………………..,

<columnN> <datatype>

table_constraints like PRIMARY KEY, FOREIGN KEY

);

Example:

CREATE TEMPORARY TABLE StudentDetailsTemp(
  sid int PRIMARY KEY, 
  sname varchar(255),
  age int
);

Output:

2. How to Insert into Temporary Table?

The syntax to insert into temporary table is exactly same as any normal table.

Syntax:

INSERT INTO table_name(<column1>,<column2>,………..)

VALUES(<value1>,<value2>,……….);

OR

INSERT INTO table_name(<column1>,<column2>,………..)

SELECT <column1>,<column2>,……

FROM <table_name>;

Example:

Insert students’ details who are from “IIT Hyderabad” University from the StudentDetails table created above the into a temporary table named StudentDetailsTemp.

INSERT INTO StudentDetailsTemp(sid, sname, age)
SELECT sid, sname, age
FROM StudentDetails 
WHERE university = "IIT Hyderabad";

Output:

3. How to DROP Temporary Table?

You can drop the temporary table just like how we drop normal table using DROP TABLE <table_name> statement. But, this way there is a risk of drop the normal table instead of temporary table which has same name as normal table.

To drop the temporary table, you can use the DROP TEMPORARY TABLE <table_name> statement which removes temporary table named <table_name> only, but not a regular table.

Moreover, even if you do not drop the temporary table manually by running the query, it is dropped automatically once your current session is closed.

Syntax:

DROP TEMPORARY TABLE <table_name>;

Example:

Let us drop the above-created temporary table named “StudentDetailsTemp”.

DROP TEMPORARY TABLE StudentDetailsTemp;

Output:

MySQL is an open-source RDBMS, i.e. Relational Database Management System which is maintained by Oracle. MySQL has support for major operating systems like Windows, MacOS, Linux, etc. MySQL makes it easy for users to interact with your relational databases, which store data in the form of tables. You can write SQL queries to get the required data from the databases using MySQL.

In this article, we will look at “Views in MySQL”, which act as virtual tables, and understand its advantages, as well as the syntax of Views for creation, updation, and deletion, with the help of awesome examples.