Temporary Tables

A temporary table stores intermediate results for the duration of a session or transaction and is dropped automatically afterwards.

Concept

A temporary table stores intermediate results for the duration of a session or transaction and is dropped automatically afterwards. Unlike CTEs, temporary tables persist across multiple statements and can be indexed.

Local / session temporary table Only the creating session Until session ends (or transaction ends if configured) All major DBMSs Global temporary table

Definition visible to all sessions; data private per session (Oracle, Db2) or shared (SQL Server ##)

Oracle: definition is permanent, data per session/transaction; SQL Server ##: until the creating session ends and no one uses it

Oracle, SQL Server, Db2, PostgreSQL (not supported; extensions exist) Batch/procedure scope Until batch ends SQL Server (DECLARE @t TABLE)

Why It Matters

Break complex processing into steps with inspectable intermediate results.

Reuse an expensive intermediate result in multiple queries.

Improve performance by indexing and collecting statistics on intermediate data.

Stage data during ETL.

Syntax

-- PostgreSQL
CREATE TEMP TABLE tmp_name (...);                    -- or CREATE TEMPORARY TABLE
CREATE TEMP TABLE tmp_name ON COMMIT DROP AS SELECT ...;   -- dropped at transaction end
CREATE TEMP TABLE tmp_name ON COMMIT DELETE ROWS (...);    -- rows cleared each commit

-- MySQL
CREATE TEMPORARY TABLE tmp_name (...);
CREATE TEMPORARY TABLE tmp_name AS SELECT ...;

-- SQL Server
CREATE TABLE #tmp_name (...);          -- local (session)
CREATE TABLE ##tmp_name (...);         -- global
SELECT ... INTO #tmp_name FROM ...;
DECLARE @tbl TABLE (id INT PRIMARY KEY, amount DECIMAL(12,2));   -- table variable

-- Oracle
CREATE GLOBAL TEMPORARY TABLE tmp_name (...)
ON COMMIT PRESERVE ROWS;               -- data kept until session ends (DELETE ROWS: until commit)
-- Oracle 18c+: private temporary tables: CREATE PRIVATE TEMPORARY TABLE ora$ptt_name (...) ON COMMIT DROP DEFINITION;

-- SQLite
CREATE TEMP TABLE tmp_name (...);

Example

28.1 Session-level temporary table with index Result of the first query:

28.2 SQL Server temp table and table variable 28.3 Oracle global temporary table 28.4 Temporary table vs CTE Session/transaction; many statements Yes (tempdb/temp tablespace/memory) Indexes and statistics No (use loops) Creation, writes, potential logging

Large intermediate data reused several times; multi-step ETL Readability; single-query logic

28.5 Temporary table vs table variable (SQL Server) @table variable Yes (better plans for large data)

No column statistics (historically estimated as 1 row; SQL Server 2019 deferred compilation improves this) Any, created after the table

Only constraints / inline index definitions Data changes rolled back

Data changes not rolled back by ROLLBACK Session (visible to nested procedures) Batch / procedure only Can cause recompiles Larger data sets (thousands+ rows) Small sets (tens/hundreds of rows)

-- PostgreSQL
CREATE TEMP TABLE tmp_customer_totals AS
SELECT customer_id,
       COUNT(*)           AS n_orders,
       SUM(total_amount)  AS revenue
FROM orders
WHERE status <> 'CANCELLED'
GROUP BY customer_id;

CREATE INDEX ON tmp_customer_totals (revenue);
ANALYZE tmp_customer_totals;

-- Used by several subsequent queries
SELECT * FROM tmp_customer_totals WHERE revenue > 50000;
SELECT AVG(revenue) FROM tmp_customer_totals;

DROP TABLE IF EXISTS tmp_customer_totals;   -- optional; dropped automatically at session end

SELECT customer_id, SUM(total_amount) AS revenue
INTO #customer_revenue
FROM orders
WHERE status <> 'CANCELLED'
GROUP BY customer_id;

CREATE INDEX ix_tmp_rev ON #customer_revenue (revenue);

SELECT c.name, r.revenue
FROM #customer_revenue r
JOIN customers c ON c.customer_id = r.customer_id
ORDER BY r.revenue DESC;

DROP TABLE #customer_revenue;

-- Table variable
DECLARE @top_products TABLE (product_id INT PRIMARY KEY, units INT);
INSERT INTO @top_products
SELECT product_id, SUM(quantity) FROM order_items GROUP BY product_id;
SELECT * FROM @top_products WHERE units >= 2;

CREATE GLOBAL TEMPORARY TABLE gtt_order_staging (
    order_id      NUMBER,
    customer_id   NUMBER,
    total_amount  NUMBER(12,2)
) ON COMMIT DELETE ROWS;

-- Each session sees only its own rows; rows vanish at COMMIT.
INSERT INTO gtt_order_staging VALUES (9001, 1, 500);
SELECT COUNT(*) FROM gtt_order_staging;   -- 1 in this session, 0 in others
COMMIT;
SELECT COUNT(*) FROM gtt_order_staging;   -- 0

Expected Result

In a second session, SELECT * FROM tmp_nums; fails with "relation tmp_nums does not exist".

-- PostgreSQL
CREATE TEMP TABLE tmp_nums (n INT);
INSERT INTO tmp_nums VALUES (1), (2), (3);
SELECT SUM(n) AS total FROM tmp_nums;

Common Mistakes

Expecting another session (or a connection pool's different connection) to see your temporary table.

Name collisions: in PostgreSQL and MySQL, a temp table with the same name hides a permanent table in the same session.

Leaving temp tables in long-lived pooled connections (memory/tempdb growth); drop them explicitly.

Using table variables for large data sets in SQL Server.

Using temp tables where a single CTE query is sufficient.

Best Practices

Use a clear prefix (tmp_, #).

Index temp tables that are joined or filtered repeatedly, and analyze them.

Drop temp tables when done in pooled environments.

Monitor tempdb/temporary tablespace usage.

Interview Relevance

Medium: "Temp table vs CTE?", "Local vs global temp table?", "Temp table vs table variable in SQL Server?".

Practice Questions

Create a temp table with monthly revenue and query it twice.

Explain why a temp table created by one API request is not visible in the next request.

Convert a three-step temp-table process into a single CTE query.

When would a table variable be a poor choice?

Create an Oracle global temporary table whose rows persist until the session ends.