MySQL Temporary Table

A temporary table in MySQL is a special type of table that stores data temporarily for the current database session. Only the session that creates the table can access it.

We generally use temporary tables to store intermediate results, perform analytics, simplify complex SQL queries, improve query readability, and process data without affecting permanent tables.

When the database session ends, MySQL automatically removes the temporary table. You can also delete it manually whenever you no longer need it.

Key Features of MySQL Temporary Table

There are the following key features of a MySQL temporary table:

• A MySQL temporary table exists only during the current database session.
• MySQL automatically deletes the temporary table when the database session ends.
• A temporary table is visible only to the session that created it.
• It does not affect permanent database tables unless you explicitly use it to modify them.
• It can contain indexes, constraints, and multiple columns, just like a regular table.
• It supports INSERT, UPDATE, DELETE, and SELECT statements.
• You can manually remove it using the DROP TEMPORARY TABLE statement before the session ends.
• A temporary table can have the same name as an existing permanent table. In that session, the temporary table hides the permanent table with the same name until the temporary table is dropped or the session ends.

Syntax of MySQL Temporary Table

The general syntax to create a MySQL temporary table is:

CREATE TEMPORARY TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype
);

In this syntax:

• CREATE TEMPORARY TABLE creates a temporary table that is available only during the current database session.
• table_name specifies the name of the temporary table.
• column1, column2, column3, etc. define the table columns and their data types.

After creating the temporary table, you can insert, update, delete, and retrieve data just as you would with a regular MySQL table. MySQL automatically drops the temporary table when the current session ends.

Rules for MySQL Temporary Tables

There are the following rules that you should remember while creating temporary tables in MySQL:

1. A temporary table name can match an existing permanent table name.
2. The temporary table hides the permanent table with the same name during the session.
3. Only the creating session can access the temporary table.
4. MySQL removes the table automatically when the session closes.
5. You can also delete it manually using DROP TEMPORARY TABLE.
6. Temporary tables fully support indexes and most constraints (PRIMARY KEY, UNIQUE, NOT NULL), but they do not support FOREIGN KEY constraints.

Create a Temporary Table in MySQL

The following SQL statement creates a temporary table named EmployeeTemp with three columns: employee_id, employee_name, and salary.

CREATE TEMPORARY TABLE EmployeeTemp (
    employee_id INT,
    employee_name VARCHAR(50),
    salary DECIMAL(10,2)
);

In this example query:

• EmployeeTemp is the name of the temporary table.
• employee_id stores the employee’s unique ID as an integer.
• employee_name stores the employee’s name with a maximum length of 50 characters.
• salary stores the employee’s salary with up to 10 digits, including 2 digits after the decimal point.
• The temporary table remains available only during the current database session.

Insert Data into a Temporary Table

Let’s insert data into a temporary table using an INSERT INFO statement.

INSERT INTO EmployeeTemp
VALUES
(101, 'Deepak', 45000.00),
(102, 'Tripti', 52000.00),
(103, 'Priya', 61000.00);

In this SQL query:

• The INSERT INTO statement adds three rows to the EmployeeTemp temporary table.
• Each row contains an employee ID, an employee name, and a salary.
• The data is stored only in the temporary table for the current database session.
• The inserted records remain available until you manually drop the temporary table or the database session ends.
• This statement does not insert or modify data in any permanent table.

Retrieve Data from a Temporary Table

The following SELECT statement retrieves all records from the EmployeeTemp temporary table.

SELECT * FROM EmployeeTemp;

Output:

In the above SQL query:

• The SELECT * statement retrieves all columns from the EmployeeTemp table. The asterisk (*) is the standard SQL notation for selecting every column in the referenced table.
• The FROM clause specifies the temporary table from which MySQL retrieves the data.
• MySQL returns all rows currently stored in the temporary table because there is no WHERE clause filtering the results.
• The data remains available only during the current database session.
• If the temporary table has been dropped or the session has ended, MySQL returns an error because the table no longer exists in the database.

Create a Temporary Table from Another Table

Instead of defining the columns manually, you can create a temporary table by copying selected data from an existing table. MySQL creates the temporary table using the columns returned by the SELECT statement and inserts the matching rows into it.

Suppose you already have an employees table.

CREATE TEMPORARY TABLE HighSalaryEmployees
SELECT employee_id,
       employee_name,
       salary
FROM employees
WHERE salary > 50000;

In the above SQL query:

• The CREATE TEMPORARY TABLE statement creates a temporary table named HighSalaryEmployees.
• The SELECT statement retrieves the employee_id, employee_name, and salary columns from the employees table.
• The WHERE salary > 50000 condition selects only employees whose salary is greater than 50000.
• MySQL automatically creates the columns in the temporary table based on the columns returned by the SELECT statement.
• MySQL also copies all matching rows into the temporary table during its creation.
• The original employees table remains unchanged because MySQL only reads data from it.
• The HighSalaryEmployees table exists only during the current database session. MySQL automatically removes it when the session ends.

View Data from the MySQL Temporary Table

Now we will use the following statement to retrieve all records stored in the HighSalaryEmployees temporary table.

SELECT * FROM HighSalaryEmployees;

In this SQL query, MySQL returns all employee records that were copied from the employees table during the table’s creation. Retrieving data from the temporary table does not modify either the temporary table or the original employees table.

How to Update Data in a Temporary Table?

In MySQL, you can update data in a temporary table just as you would in a regular table.

UPDATE EmployeeTemp
SET salary = salary + 5000
WHERE employee_id = 101;

In this SQL query:

• The UPDATE statement modifies the existing data in the EmployeeTemp temporary table.
• The SET salary = salary + 5000 expression increases the current salary by 5000.
• The WHERE employee_id = 101 condition ensures that MySQL updates only the employee whose ID is 101.
• The salary values of all other employees remain unchanged.
• This update affects only the EmployeeTemp temporary table and does not modify any permanent table.

How to Delete Rows from a Temporary Table?

In MySQL, you can delete one or more rows from a temporary table using the DELETE statement.

DELETE FROM EmployeeTemp
WHERE employee_id = 102;

In this SQL query:

• The DELETE statement removes rows from the EmployeeTemp temporary table.
• The WHERE employee_id = 102 condition identifies the row to delete.
• MySQL deletes only the matching employee record. The remaining rows stay unchanged.
• This operation affects only the temporary table.
• The original permanent table, if any, remains unchanged because the deletion occurs only in the temporary table.

How to Drop a Temporary Table in MySQL?

In MySQL, you can manually remove a temporary table before the current database session ends by using the DROP TEMPORARY TABLE statement.

DROP TEMPORARY TABLE EmployeeTemp;

In this SQL query:

• The DROP TEMPORARY TABLE statement permanently removes the EmployeeTemp temporary table from the current database session.
• MySQL immediately deletes the table and all the data stored in it.
• After dropping the table, you cannot retrieve or modify its data unless you create the temporary table again.
• This statement affects only the temporary table and does not modify any permanent table in the database.
• You do not need to drop a temporary table manually. If you do not drop it, MySQL automatically removes it when the current database session ends.

Best Practices for Temporary Tables in MySQL

There are the following key points that you should follow for the best practices for temporary tables in MySQL:

• Drop temporary tables when you finish using them.
• Use meaningful table names.
• Create indexes for large temporary tables when needed.
• Avoid storing unnecessary data.
• Select only the required columns instead of using SELECT * where possible.
• Use temporary tables only when they simplify your queries.