Temporary Table in MySQL

Summary: In this post, we will discuss everything about the MySQL Temporary table.

Introduction to MySQL Temporary Table

A temporary table in MySQL is a special type of table which is used to store the temporary set of results that can be reused multiple times without running any expensive SQL query.

A Temporary Table is visible only within the current session and is automatically dropped when the session is closed.

Recommended: MySQL CREATE TABLE

The general syntax to create a temporary table in MySQL is:

CREATE TEMPORARY TABLE table_name(
   column_1_definition,
   column_2_definition,
   ...,
   table_constraints
);

Note: Replace table_name with the name of the table

Recommended: MySQL CREATE TABLE … SELECT

To create a temporary table based on the definition of another table, use this syntax instead:

CREATE TEMPORARY TABLE new_table SELECT * FROM original_table LIMIT 0;

Some of the prominent properties of MySQL Temporary Table are:

• A Temporary Table is visible only within the current session therefore two different sessions can use the same temporary table name without conflicting with each other.
• A temporary table can have the same name as an existing non-temporary table. However, two Temporary tables in the same session cannot have the same name.
• MySQL automatically deletes all the Temporary Tables for the current session when the session ends. Also, we can explicitly remove any existing Temporary table using the MySQL DROP TABLE statement.
• A temporary table like any other normal table is stored in the disk and is fetched to the memory for processing.

The temporary table is useful when we need to store immediate results within the MySQL server for use over a number of SQL transactions. For example, storing the results of the JOIN statement for reuse.

Let’s see an example to have a better understanding of the Temporary Table.

Example

Consider the following two tables in MySQL.

The ‘programming.roll_no’ is referencing ‘students.roll_no’.

To get all student’s details using the two tables, we will have to use the following SELECT statement along with the JOIN clauses:

SELECT s.roll_no, s.name, p.language FROM 
students s INNER JOIN programming p 
ON p.roll_no = s.roll_no;

In the case of a large database, It would be inefficient for the MySQL server to process the same query again and again.

So it is better to store the results in a temporary table and query data from the same.

CREATE TEMPORARY TABLE temp_students_details
SELECT s.roll_no, s.name, p.language FROM 
students s INNER JOIN programming p 
ON p.roll_no = s.roll_no;

Query OK, 1 row affected (0.20 sec)

SELECT * from temp_students_details;

Drop MySQL Temporary Table

The general syntax to drop or delete a temporary table in MySQL is:

DROP TEMPORARY TABLE teble_name;

Example

Recommended: MySQL DROP TABLE

Deleting the ‘temp_student_details’ table which we have created in the previous example.

DROP TEMPORARY TABLE temp_student_details;

Query OK, 0 rows affected (0.01 sec)

The best practice for naming a temporary table is to prefix the table name with ‘temp‘ word.

Although a temporary table can have the same name as a non-temporary table, it is not recommended because it may lead to ambiguity and could cause a loss of data.

For example, in the case of server disconnection, all the temporary tables will automatically be deleted by MySQL. The user unaware of the deletion will end up deleting a non-temporary table of the same name.

In this tutorial, we have learned about the MySQL temporary table. We discussed why do we use a temporary table and how to create and delete a temporary table in MySQL.