When a report needs an intermediate result that several later statements will reuse, such as each student's total fees, you can save it in a temporary table. A temporary table behaves like a normal table, with one difference: it belongs to your connection alone and disappears when that connection closes. Nobody else can see it, and you do not have to clean it up.
Create one from a query
CREATE TABLE payments (
pay_id INT PRIMARY KEY,
student VARCHAR(20),
course VARCHAR(10),
paid_on DATE,
amount DECIMAL(10,2)
);
INSERT INTO payments VALUES
(1,'Deepak','SQL','2026-10-03',4000), (2,'Ira','Python','2026-10-05',6000),
(3,'Deepak','SQL','2026-10-20',4000), (4,'Manav','Excel','2026-11-02',3000),
(5,'Ira','Python','2026-11-04',6000), (6,'Zoya','SQL','2026-11-06',4000);
CREATE TEMPORARY TABLE tmp_student_totals AS
SELECT student, SUM(amount) AS total_paid, COUNT(*) AS instalments
FROM payments
GROUP BY student;
SELECT * FROM tmp_student_totals ORDER BY total_paid DESC;
| student | total_paid | instalments |
|---|---|---|
| Ira | 12000.00 | 2 |
| Deepak | 8000.00 | 2 |
| Zoya | 4000.00 | 1 |
| Manav | 3000.00 | 1 |
You can also declare the structure first, with keys and an engine, and fill it later:
CREATE TEMPORARY TABLE tmp_ids (id INT PRIMARY KEY) ENGINE=InnoDB;
INSERT INTO tmp_ids VALUES (2), (5);
SELECT p.* FROM payments p JOIN tmp_ids t ON t.id = p.pay_id;
| pay_id | student | course | paid_on | amount |
|---|---|---|---|---|
| 2 | Ira | Python | 2026-10-05 | 6000.00 |
| 5 | Ira | Python | 2026-11-04 | 6000.00 |
This pattern is useful when an application has a list of IDs (from a file or an API) and needs to join it against real tables. It beats building a huge IN (...) list.
Five behaviours that surprise people
1. SHOW TABLES does not list it
SHOW TABLES;
| Tables_in_tt |
|---|
| payments |
The temporary table exists, and you just queried it, but SHOW TABLES lists only permanent tables. In MySQL 8.0, information_schema.INNODB_TEMP_TABLE_INFO lists active InnoDB temporary tables.
2. You cannot refer to it twice in one query
SELECT student, total_paid
FROM tmp_student_totals
WHERE total_paid > (SELECT AVG(total_paid) FROM tmp_student_totals);
ERROR 1137 (HY000): Can't reopen table: 'tmp_student_totals'
This is a long-standing MySQL limit on TEMPORARY tables. It affects self-joins, subqueries on the same table, and UNIONs that read it twice. If you need the result more than once in a single statement, a CTE is the cleaner tool:
WITH t AS (
SELECT student, SUM(amount) AS total_paid FROM payments GROUP BY student
)
SELECT student, total_paid FROM t
WHERE total_paid > (SELECT AVG(total_paid) FROM t);
| student | total_paid |
|---|---|
| Deepak | 8000.00 |
| Ira | 12000.00 |
The average of the four totals is 6,750, so Deepak and Ira qualify. CTEs are explained in common table expressions in MySQL. You can also copy the temporary table into a second temporary table and join the two.
3. It can hide a real table with the same name
CREATE TEMPORARY TABLE payments (note VARCHAR(20));
SELECT * FROM payments; -- Empty set: this is the temporary one
DROP TEMPORARY TABLE payments;
SELECT COUNT(*) FROM payments; -- the real table is back
| COUNT(*) |
|---|
| 6 |
While the temporary table exists, every statement in your session that names payments uses it instead of the real one. This is dangerous with DELETE or DROP. Prefix temporary names (tmp_), and always write DROP TEMPORARY TABLE, which can never drop a permanent table by mistake.
4. Connection pools keep it alive
"Disappears when the session ends" assumes the session ends. Web apps usually reuse pooled connections, so a temporary table created during one request can still exist when the next request picks up that connection. Drop it explicitly when you are done:
DROP TEMPORARY TABLE IF EXISTS tmp_ids, tmp_student_totals;
5. It needs a privilege
Creating one needs the CREATE TEMPORARY TABLES privilege on the database. Read-only reporting users often lack it, which shows up as an "access denied" error. See MySQL user management and privileges.
Temporary table, CTE, view or derived table?
| Option | Lives for | Visible to | Good for |
|---|---|---|---|
| Temporary table | The session (or until dropped) | Only your connection | A result reused by several statements; indexing an intermediate result |
CTE (WITH) | One statement | That statement | Readable multi-step queries; referring to a step twice |
| Derived table | One statement | That statement | A one-off subquery in FROM |
| View | Until dropped | Everyone with privileges | A saved query shared across users and tools |
A temporary table stores the result at the moment you create it. If payments changes afterwards, tmp_student_totals does not change. A view runs its query fresh each time; see the MySQL views tutorial. Do not confuse either with the internal temporary tables MySQL creates on its own for some GROUP BY or UNION queries. Those show up in EXPLAIN as "Using temporary", and you never create or name them.
Temporary tables are also handy inside stored procedures that build a report in steps.