Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Data Definition

SQL temporary tables

Reference for SQL temporary tables: CREATE TEMP TABLE, how long they live, who can see them, and how PostgreSQL, MySQL, SQL Server and Oracle differ.

A temporary table is an ordinary-looking table that exists only for your session (or, in some databases, your transaction). Other connections cannot see it, and the database removes it automatically when the session ends. It is a convenient scratch area for multi-step processing, where a CTE would be too short-lived or a real table too heavy.

Temporary tables can hold many rows, be indexed and be updated, unlike a CTE, which exists for one statement only.

SQL
CREATE TEMP TABLE big_orders AS
SELECT * FROM orders WHERE shipping > 5;

Try it

SQL Work in two steps with a temp table ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL Temp tables live in their own schema ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
CREATE {TEMP | TEMPORARY} TABLE [IF NOT EXISTS] table_name (column_definitions);
CREATE {TEMP | TEMPORARY} TABLE table_name AS select_statement;
Part Description
TEMP / TEMPORARY Makes the table session-private. Both keywords mean the same.
ON COMMIT ... PostgreSQL and Oracle: PRESERVE ROWS (default in PostgreSQL), DELETE ROWS or DROP.

Dialect differences

Dialect Behavior
SQLite CREATE TEMP TABLE; stored in the temp schema (a separate temporary database file or memory); dropped when the connection closes.
PostgreSQL CREATE TEMP TABLE; dropped at session end by default, or at commit with ON COMMIT DROP.
MySQL CREATE TEMPORARY TABLE; visible only to the session; can shadow a real table; cannot be reopened twice in one query.
SQL Server Name starts with # (local, session) or ## (global): CREATE TABLE #scratch (...). Also table variables (DECLARE @t TABLE).
Oracle Global temporary tables: CREATE GLOBAL TEMPORARY TABLE. The definition is permanent, but data is private per session or transaction.
SQL
-- SQL Server
SELECT customer_id, COUNT(*) AS orders
INTO #order_counts
FROM orders
GROUP BY customer_id;

-- Oracle
CREATE GLOBAL TEMPORARY TABLE scratch (id NUMBER, note VARCHAR2(50))
ON COMMIT PRESERVE ROWS;

Usage notes

  • A temporary table can hide a real one with the same name for the rest of your session. Pick distinctive names.
  • Not shared. Another connection (or a connection pool handing you a different session) will not find your table.
  • Good for staging: load, clean, join, then write the final result into a permanent table.
  • Cost: they use disk or memory and may need statistics or indexes like any table; add an index for big ones.
  • Cleanup: they vanish on their own, but DROP TABLE frees space sooner in long sessions.
  • Permissions: you usually need the right to create temporary objects, which is granted separately.