Skip to content

Create a temporary table

What is a temporary table

A temporary table is a special type of table that is only visible in the current session after creation. At the end of the current session, the database automatically deletes the temporary table and frees up all space. You can also use DROP TABLE to delete the temporary table.

You can use a temporary table to save some intermediate results in a single session, for example, you may need to query these results multiple times, or these results are a subset of other queries.

Advantages of temporary tables

Temporary tables have many uses and advantages in database design:

-Data Isolation: Temporary tables are independent in each session or transaction. This means that two temporary tables with the same name can exist in two different sessions and will not affect each other.

-Simplify complex queries: If a query is very complex and involves multiple joins and subqueries, you can save the query results to a temporary table and then operate on this temporary table, thereby simplifying the query and improving performance.

-Improving performance: For complex queries on large data sets, storing data in temporary tables can significantly improve query performance. Because temporary tables are stored in memory, access speed is fast.

-Protecting Data: Using temporary tables can avoid modifying the original data. When you need to perform operations that may change the original data, you can first save the data in a temporary table and then operate on the temporary table, which can avoid errors in the original data.

-Save storage space: Temporary tables are automatically deleted when they are no longer needed, which saves storage space.

-Helps for debugging: In complex nested queries, temporary tables can be used to store intermediate results to help debug and verify output results at each step.

Note that temporary tables are not omnipotent, they also have some limitations, such as only accessing in the current session, and once the session ends, the temporary table will disappear.

Prepare before starting

Before reading this page, you need to prepare the following:

How to use temporary tables

The syntax of using temporary tables is the same as that of regular tables, except that the TEMPORARY keyword is added before the statement that creates the table:

CREATE TEMPORARY TABLE temp_table_name (column_list);

You can use the same table names in temporary and regular tables without conflict because they are actually in different namespaces. However, in the same session, two temporary tables cannot share the same name.

Note

  1. Even if a temporary table can have the same name as a permanent table, it is not recommended. Because this may lead to unexpected data loss. For example, if the connection to the database server is lost and you automatically reconnect to the server, you cannot distinguish between temporary and permanent tables. If you issue another DROP TABLE statement at this time, the deleted permanent table may be a temporary table instead of a temporary table, and this result is unpredictable.
  2. When using the SHOW TABLES command to display the data table list, you cannot see the temporary table list either.

Example

-- Create a temporary table 'temp_employees'
CREATE TEMPORARY TABLE temp_employees (
    employee_id INT AUTO_INCREMENT, -- Self-increased employee ID
    first_name VARCHAR(50), -- employee name
    last_name VARCHAR(50), -- employee last name
    email VARCHAR(100), -- employee email address
    PRIMARY KEY (employee_id) -- Set 'employee_id' as the primary key
);

-- Insert some data into the 'temp_employees' table
INSERT INTO temp_employees (first_name, last_name, email)
VALUES ('John', 'Doe', 'john.doe@example.com'),
       ('Jane', 'Doe', 'jane.doe@example.com'),
       ('Jim', 'Smith', 'jim.smith@example.com'),
       ('Jack', 'Johnson', 'jack.johnson@example.com'),
       ('Jill', 'Jackson', 'jill.jackson@example.com');

-- Query the temporary table and view all employee information
SELECT * FROM temp_employees;
+------------------------------------------------------------------------------------------------------------------------------
| employee_id | first_name | last_name | email |
+------------------------------------------------------------------------------------------------------------------------------
| 1 | John | Doe | john.doe@example.com |
| 2 | Jane | Doe | jane.doe@example.com |
| 3 | Jim | Smith | jim.smith@example.com |
| 4 | Jack | Johnson | jack.johnson@example.com |
| 5 | Jill | Jackson | jill.jackson@example.com |
+------------------------------------------------------------------------------------------------------------------------------
5 rows in set (0.01 sec)

-- Note: At the end of this session, the temporary table 'temp_employees' will be automatically deleted