Temporary Tables

Introduction

Sometimes a task is too complex to solve with a single query. You might need to calculate some intermediate numbers first, store them somewhere, and then run further queries against that intermediate result. Creating a brand-new permanent table just for this throwaway purpose would clutter your database forever.

MySQL solves this problem with temporary tables — tables that exist only for the lifetime of your current session and vanish automatically afterward, leaving no cleanup for you to do.

What is a Temporary Table?

A temporary table is a special kind of table that is automatically dropped (deleted) as soon as the database session or connection that created it is closed. While it exists, you can use it exactly like a normal table — you can insert into it, select from it, update it, and join it with other tables.

The key difference is visibility: a temporary table is only visible to the exact connection that created it. If two different users, or two different application connections, each create a temporary table with the same name, they do not clash — each connection sees only its own private copy.

Why Use Temporary Tables?

Temporary tables shine whenever a task needs to happen in stages. Rather than writing one giant, hard-to-read query with deeply nested subqueries, you can break the problem down: store an intermediate result in a temporary table, then run simpler follow-up queries against it.

Creating a Temporary Table

You create a temporary table with the CREATE TEMPORARY TABLE statement. The syntax for defining columns is identical to a normal CREATE TABLE statement — the only difference is the TEMPORARY keyword.

CREATE TEMPORARY TABLE high_value_orders (
    order_id INT,
    customer_name VARCHAR(50),
    total_amount DECIMAL(10, 2)
);

Imagine you need to find all orders worth more than $500, and then run several different reports against just that subset. Instead of repeating the same filtering logic in every report query, you can filter once into a temporary table.

INSERT INTO high_value_orders (order_id, customer_name, total_amount)
SELECT order_id, customer_name, total_amount
FROM orders
WHERE total_amount > 500;

Working with a Temporary Table

Once created and populated, you can query a temporary table exactly like any other table. Here we run two separate "reports" against the same intermediate data, without ever touching the original, much larger orders table again.

-- Report 1: How many high-value orders are there in total?
SELECT COUNT(*) AS total_high_value_orders
FROM high_value_orders;

-- Report 2: List them, highest first
SELECT customer_name, total_amount
FROM high_value_orders
ORDER BY total_amount DESC;

+-------------------------+
| total_high_value_orders |
+-------------------------+
| 3                       |
+-------------------------+

+---------------+--------------+
| customer_name | total_amount |
+---------------+--------------+
| Priya Shah    | 980.00       |
| Alex Turner   | 725.50       |
| Sam Lee       | 512.00       |
+---------------+--------------+

You can also update rows, add indexes, or even join a temporary table with a permanent one, just as you would with any regular table.

SELECT h.customer_name, h.total_amount, c.email
FROM high_value_orders h
JOIN customers c ON c.name = h.customer_name;

What Happens When the Session Ends

The defining feature of a temporary table is that it disappears automatically once your session ends — whether that means closing your MySQL Workbench connection, disconnecting your application, or the connection timing out.

-- While your session is open:
SELECT * FROM high_value_orders;   -- works fine, returns rows

-- ...after you disconnect and reconnect (a brand new session)...

SELECT * FROM high_value_orders;   -- ERROR!

ERROR 1146 (42S02): Table 'shop.high_value_orders' doesn't exist

This is expected and correct behavior — not a bug. The table was never meant to survive past your session. If you need the data to persist permanently, insert it into a normal, permanent table instead.

Temporary Tables and Naming

Because a temporary table is only visible within the session that created it, it can safely share the exact same name as a permanent table. MySQL will use the temporary one whenever both exist, for the duration of that session.

CREATE TABLE orders (id INT, total DECIMAL(10,2));       -- permanent table
CREATE TEMPORARY TABLE orders (id INT, total DECIMAL(10,2)); -- temporary table, same name

SELECT * FROM orders;   -- refers to the TEMPORARY table while it exists
DROP TEMPORARY TABLE orders;
SELECT * FROM orders;   -- now refers to the permanent table again

Common Mistakes

Best Practices

Frequently Asked Questions

Key Takeaways

Summary

Temporary tables give you a safe, self-cleaning scratch space for breaking complex tasks into simpler steps, without permanently cluttering your database schema.

In this lesson, you learned what a temporary table is, why it is useful, how to create and populate one, and what happens to it once your session ends. Next, you will learn how to move data in and out of MySQL entirely, by importing and exporting databases.