Temporary Tables and Session Scope

Concept

A temporary table is a real table - it holds data, it can be indexed, it can be queried repeatedly - but it exists only for the session that created it and disappears when that session ends.

Syntax

-- MySQL, MariaDB, PostgreSQL
CREATE TEMPORARY TABLE temp_name (
    column_definitions
);

-- Create and populate from a query
CREATE TEMPORARY TABLE temp_name AS
SELECT ...;

DROP TEMPORARY TABLE IF EXISTS temp_name;

-- SQL Server uses a naming convention instead of a keyword
-- CREATE TABLE #local_temp  (...)    session scoped
-- CREATE TABLE ##global_temp (...)   visible to all sessions

Example

-- Step 1: materialise an expensive intermediate result once
CREATE TEMPORARY TABLE tmp_customer_totals AS
SELECT o.customer_id,
       COUNT(*)   AS order_count,
       SUM(o.total) AS lifetime_value,
       MAX(o.order_date) AS last_order
FROM   orders o
WHERE  o.status <> 'cancelled'
GROUP BY o.customer_id;

-- Step 2: index it, because it will be joined several times
CREATE INDEX idx_tmp_customer ON tmp_customer_totals (customer_id);

-- Step 3: reuse it as many times as you like
SELECT c.name, t.order_count, t.lifetime_value
FROM   customers c
JOIN   tmp_customer_totals t ON t.customer_id = c.id
ORDER BY t.lifetime_value DESC;

SELECT c.country, SUM(t.lifetime_value) AS country_value
FROM   customers c
JOIN   tmp_customer_totals t ON t.customer_id = c.id
GROUP BY c.country;

DROP TEMPORARY TABLE IF EXISTS tmp_customer_totals;

Explanation

The aggregate runs once. Written as a CTE referenced by two separate statements, it would run twice - a CTE cannot span statements at all. That is the case temporary tables exist for: an expensive intermediate result needed by several later queries.

The index in step 2 is the other half of the argument. A CTE and a derived table cannot be indexed; a temporary table can, and on a large intermediate result that difference can be the whole runtime.

Scope and lifetime

-- PostgreSQL: control what a commit does to the rows
CREATE TEMPORARY TABLE tmp_batch (id INT) ON COMMIT DELETE ROWS;
CREATE TEMPORARY TABLE tmp_scratch (id INT) ON COMMIT DROP;

Where the data lives

Temporary tables are written to a temporary area on disk (or memory, depending on engine and size). They can still be large, still consume disk, and in MySQL still count against the connection's tmp_table_size before spilling. A temporary table is not free just because it is temporary.

Important rules

• A temporary table is dropped automatically when the session ends, including when a connection pool recycles the connection.
• In MySQL, a temporary table with the same name as a permanent table hides it for that session - and SHOW TABLES does not list temporary tables.
• MySQL cannot refer to the same temporary table twice in one statement (no self join on it) before version 8.0.13.
• Temporary tables are not replicated in row based replication, which can break replicas if a procedure depends on one.
• SQL Server's #temp is session scoped, ##temp is global; Oracle uses global temporary tables whose definition is permanent and whose data is session private.

Common mistakes

• Creating a temporary table with the same name as a real one and then wondering why the data looks wrong.
• Forgetting to drop it in a long lived connection, so the next request in a pooled connection sees stale data.
• Using one for a single query where a CTE or derived table would do.
• Not indexing it, then joining it several times.
• Relying on one inside a procedure that runs on a replica.

Best practices

• Prefix names clearly: tmp_.
• Always DROP TEMPORARY TABLE IF EXISTS at the start and end of any procedure that uses one, especially with connection pooling.
• Index it if it is joined or filtered more than once.
• Populate it with CREATE TEMPORARY TABLE ... AS SELECT so the types come from the query.
• Keep it as small as possible - filter before materialising, not after.

Practice

1. Materialise per product sales totals into a temporary table and use it in two different reports.
2. Add the index that makes both reports fast, and explain which join it serves.
3. Why is dropping the table at the start of a procedure as important as dropping it at the end?