Temporary Table

A temporary table in MySQL is a table that exists only for the duration of a session (or until explicitly dropped).

• Automatically dropped when:
  • The session (connection) ends.
  • Or you explicitly DROP it.
• They don’t interfere with normal tables having the same name.
• Each session gets its own version of the temporary table.
• It used for storing intermediate results or complex calculations within a session.

Advantages of Temporary Tables

• Session isolation → Each session gets its own copy (safe for concurrent users).
• Simplifies complex queries by breaking them into steps.
• Good for intermediate results → useful in reporting, analytics, or stored procedures.
• No name conflict with permanent tables (you can have both employees and TEMPORARY employees).
• Automatic cleanup → dropped at session end.

Limitations of Temporary Tables

• Session-only → cannot share between different connections.
• Performance overhead → large temporary tables may use disk instead of memory.
• Limited features:
  • No FOREIGN KEY constraints allowed.
  • Temporary tables cannot be indexed with FULLTEXT or SPATIAL indexes. If you create a temporary table with the same name as an existing permanent table in your session, the temporary table takes priority. (Permanent table becomes hidden for that session until temp table is dropped.)

Creating and Using a Temporary Table

Suppose we have an employees table:

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10,2)
);

Now, create a temporary table to hold only IT employees with salary above 50000:

CREATE TEMPORARY TABLE high_paid_it AS
SELECT emp_id, name, salary
FROM employees
WHERE department = 'IT' AND salary > 50000;

Query the temporary table:

SELECT * FROM high_paid_it;

This works like a regular table, but it exists only in this session.

If you open another MySQL session and try:

SELECT * FROM high_paid_it;

You’ll get an error: ERROR 1146 (42S02): Table 'high_paid_it' doesn't exist

Because temporary tables are session-specific.

You can modify, drop, join temporary table like regular table.