#1 India's Top IT Training Institute
×

About Uncodemy

Know more about who we are and what we stand for.

About Us

About Us

Discover our mission to equip individuals with essential IT skills and industry expertise.

Read More →
UnCodeMy Gallery

UnCodeMy Gallery

Explore highlights from our IT training sessions and success stories.

Read More →
Mentors

Mentors

Meet the industry experts who train and guide you through your course.

Read More →
Free Resources

Free Resources

Free tutorials, interview questions, tools and calculators to help you learn.

Explore →
×

Free Resources

Learn, practice and plan your career — bilkul free.

Tutorials

Tutorials

Step-by-step written tutorials to learn any technology at your own pace.

Read More →
Free Counselling

Free Counselling

Book a free video counselling session and pick the right course for you.

Read More →
Online Compiler

Online Compiler

Write, run and test your code online — no setup required.

Read More →
MySQL Tutorial · Data Analytics

Temporary Tables in MySQL: Usage and Examples

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;
studenttotal_paidinstalments
Ira12000.002
Deepak8000.002
Zoya4000.001
Manav3000.001

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_idstudentcoursepaid_onamount
2IraPython2026-10-056000.00
5IraPython2026-11-046000.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);
studenttotal_paid
Deepak8000.00
Ira12000.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?

OptionLives forVisible toGood for
Temporary tableThe session (or until dropped)Only your connectionA result reused by several statements; indexing an intermediate result
CTE (WITH)One statementThat statementReadable multi-step queries; referring to a step twice
Derived tableOne statementThat statementA one-off subquery in FROM
ViewUntil droppedEveryone with privilegesA 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.

Become a Job-Ready Data Analyst

Master MySQL, Advanced Excel, Power BI, Tableau and Python with Uncodemy's Data Analytics Training in Noida — live projects, expert mentors and Dedicated Placement Support.

Enroll in Data Analytics Course →