MySQL Temporary Table

Last updated: October 19, 2026Oct 19, 2026 5 min read

A temporary table is a table that exists only for the current connection (session). It is perfect for holding intermediate results during a calculation, a report or a data import. When you disconnect, MySQL drops it automatically, and other sessions never see it.

1. What Makes a Table Temporary

• Created with CREATE TEMPORARY TABLE.
• Visible only to the session that created it.
• Dropped automatically when the session ends (or with DROP TEMPORARY TABLE).
• Two sessions can each have a temporary table with the same name without conflict.
• It doesn't appear in SHOW TABLES.

2. Creating and Using a Temporary Table

CREATE TEMPORARY TABLE [IF NOT EXISTS] name (column definitions…);

Store each student's average, then query it several times without recalculating:

CREATE TEMPORARY TABLE student_avg (
  student   VARCHAR(20) PRIMARY KEY,
  avg_score DECIMAL(5,2)
);

INSERT INTO student_avg
SELECT student, AVG(score) FROM marks GROUP BY student;

SELECT * FROM student_avg ORDER BY avg_score DESC;

Result: 4 rows

SELECT student FROM student_avg WHERE avg_score >= 80;

Result: 2 rows

SHOW TABLES;

SHOW TABLES doesn't list it

3. Creating a Temporary Table from a Query

Combine CREATE TEMPORARY TABLE with AS SELECT to create and fill it in one step:

CREATE TEMPORARY TABLE toppers AS
SELECT subject, student, score
FROM marks m
WHERE score = (SELECT MAX(score) FROM marks WHERE subject = m.subject);

SELECT * FROM toppers ORDER BY subject;

Result: 3 rows

Or copy a table's structure with LIKE: CREATE TEMPORARY TABLE marks_work LIKE marks;

4. Proving It's Private

A second connection, logged in as teacher, can't see our student_avg:

SELECT * FROM student_avg;

ERROR 1146 (42S02): Table 'school.student_avg' doesn't exist

It can create its own table with the same name, holding different data:

CREATE TEMPORARY TABLE student_avg AS
SELECT student, MAX(score) AS best FROM marks GROUP BY student;
SELECT * FROM student_avg ORDER BY student;

Result: 4 rows

Back in the first session, our own table is untouched:

SELECT COUNT(*) AS rows_in_my_table, MIN(avg_score) AS lowest_avg FROM student_avg;

Result: 1 row

5. Name Hiding: Temporary Tables Win

If a temporary table has the same name as a permanent table, the temporary one hides the permanent one for that session:

CREATE TEMPORARY TABLE marks (student VARCHAR(20), subject VARCHAR(20), score INT);
SELECT COUNT(*) AS visible_rows FROM marks;     -- the empty temporary table

DROP TEMPORARY TABLE marks;
SELECT COUNT(*) AS visible_rows FROM marks;     -- the real table again

While the temporary table exists

After DROP TEMPORARY TABLE

6. Limitations

• You can't refer to the same temporary table twice in one query, for example in a self-join: ERROR 1137: Can't reopen table.
• Temporary tables are not listed by SHOW TABLES; use information_schema.innodb_temp_table_info to see InnoDB temporary tables.
• Connection pools (in Java apps) reuse sessions, so a temporary table may survive into the next request. Drop it explicitly when done.
• Temporary tables can't have foreign keys.

SELECT a.student, b.student
FROM student_avg a JOIN student_avg b ON a.avg_score < b.avg_score;

ERROR 1137 (HY000): Can't reopen table: 'a'

7. Temporary Table or CTE?

A common table expression (WITH) gives a name to a result for one statement, with no clean-up needed, and can be referenced several times:

WITH student_avg AS (
  SELECT student, AVG(score) AS avg_score FROM marks GROUP BY student
)
SELECT a.student, COUNT(b.student) AS students_ranked_above
FROM student_avg a
LEFT JOIN student_avg b ON b.avg_score > a.avg_score
GROUP BY a.student
ORDER BY students_ranked_above;

Result: 4 rows

8. Dropping a Temporary Table

DROP TEMPORARY TABLE IF EXISTS student_avg, toppers;

Query OK, 0 rows affected

9. Summary

• CREATE TEMPORARY TABLE creates a table visible only to your session, dropped on disconnect.
• Fill it with INSERT … SELECT or create it directly with AS SELECT.
• A temporary table hides a permanent table with the same name; always drop it with DROP TEMPORARY TABLE.
• It can't be referenced twice in one query; consider a CTE for single-statement needs.
• In pooled Java connections, drop temporary tables explicitly.