TEMPORARY TABLESDDL
Buy Me A Coffee

Tables that exist only for the duration of a session or transaction and are automatically dropped when that session ends. Used to stage intermediate results without polluting the permanent schema.

Tables that exist only for the duration of a session or transaction, prefixed with CREATE TEMPORARY TABLE. Useful for intermediate results, complex transformations, and storing staging data. Some databases auto-drop on commit; others require explicit cleanup.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported 3.23 CREATE TEMPORARY TABLE creates a session-scoped table dropped automatically at session end. Temp tables are invisible to other sessions (each session gets its own private copy even if they use the same name). Cannot be referenced twice in the same query (MySQL limitation). Temp tables appear in information_schema and SHOW TABLES for the current session.
PostgreSQL ✓ Supported 6.0 CREATE TEMPORARY TABLE (or TEMP TABLE). Default lifetime is session; ON COMMIT DROP makes it transaction-scoped; ON COMMIT DELETE ROWS keeps the table structure but truncates it at each commit. PostgreSQL creates temp tables in a per-session temporary schema (pg_temp_NNN) that is searched automatically. Global temporary tables are not supported (each session gets its own isolated table). UNLOGGED tables are a related alternative for fast but non-crash-safe permanent tables.
SQL Server ✓ Supported 6.5 Two kinds: local temp tables (#table) are session-scoped and private; global temp tables (##table) are shared across sessions and dropped when the last referencing session closes. Temp tables are physically stored in tempdb. Table variables (@table) are an alternative with different scoping and statistics behavior. Temp tables support indexes and statistics; table variables have limited statistics and no column indexes.
Oracle ✓ Supported 8i Oracle uses Global Temporary Tables (GTTs): the table structure is permanent (visible in all_tables) but each session's data is private. Data lifetime is either ON COMMIT DELETE ROWS (transaction-scoped, default) or ON COMMIT PRESERVE ROWS (session-scoped). GTTs are created once and reused across sessions — no CREATE per-session. Oracle 18c added private temporary tables (CREATE PRIVATE TEMPORARY TABLE) which exist only for the session duration like other engines.
SQLite ✓ Supported 1.0 CREATE TEMPORARY TABLE (or TEMP TABLE) creates a table in the temp database, which is session-scoped and stored in a separate temp file (or in memory). Temp tables are not visible to other database connections. Dropped automatically when the connection closes.

Details

The biggest portability gap is Oracle: it uses Global Temporary Tables where the structure is a permanent schema object shared across sessions — only the data is private. Every other engine creates the temp table structure per-session. SQL Server is unique in having two tiers: session-private (#table) and cross-session global (##table). SQL Server's table variable (@table) is a common alternative but behaves differently from temp tables regarding statistics, transactions, and scope. The ON COMMIT behavior clause (DROP vs DELETE ROWS vs PRESERVE ROWS) is supported in PostgreSQL and Oracle but not MySQL, SQL Server, or SQLite.

Standard Syntax

-- Standard SQL: CREATE TEMPORARY TABLE temp_results ( id INT, result VARCHAR(255) ); INSERT INTO temp_results SELECT id, compute(val) FROM source; SELECT * FROM temp_results;

Version Support

MySQL: Since 3.23 PostgreSQL: Since 6.0 SQL Server: Since 6.5 Oracle: Since 8i SQLite: Since 1.0

Per-Database Syntax & Notes

MySQL

CREATE TEMPORARY TABLE creates a session-scoped table dropped automatically at session end. Temp tables are invisible to other sessions (each session gets its own private copy even if they use the same name). Cannot be referenced twice in the same query (MySQL limitation). Temp tables appear in information_schema and SHOW TABLES for the current session.

CREATE TEMPORARY TABLE tmp_active_users ( id INT, username VARCHAR(100) ); INSERT INTO tmp_active_users SELECT id, username FROM users WHERE active = 1; SELECT * FROM tmp_active_users; -- Dropped automatically at session end; -- or drop explicitly: DROP TEMPORARY TABLE tmp_active_users;

PostgreSQL

CREATE TEMPORARY TABLE (or TEMP TABLE). Default lifetime is session; ON COMMIT DROP makes it transaction-scoped; ON COMMIT DELETE ROWS keeps the table structure but truncates it at each commit. PostgreSQL creates temp tables in a per-session temporary schema (pg_temp_NNN) that is searched automatically. Global temporary tables are not supported (each session gets its own isolated table). UNLOGGED tables are a related alternative for fast but non-crash-safe permanent tables.

-- Session-scoped (default): CREATE TEMP TABLE tmp_active_users ( id INT, username TEXT ); -- Transaction-scoped (dropped on COMMIT/ROLLBACK): CREATE TEMP TABLE tmp_work ( val INT ) ON COMMIT DROP; -- Keep structure, truncate on commit: CREATE TEMP TABLE tmp_batch ( val INT ) ON COMMIT DELETE ROWS; -- From query: CREATE TEMP TABLE tmp_summary AS SELECT dept, SUM(salary) FROM employees GROUP BY dept;

SQL Server

Two kinds: local temp tables (#table) are session-scoped and private; global temp tables (##table) are shared across sessions and dropped when the last referencing session closes. Temp tables are physically stored in tempdb. Table variables (@table) are an alternative with different scoping and statistics behavior. Temp tables support indexes and statistics; table variables have limited statistics and no column indexes.

-- Local temp table (session-private): CREATE TABLE #tmp_active_users ( id INT, username NVARCHAR(100) ); INSERT INTO #tmp_active_users SELECT id, username FROM users WHERE active = 1; SELECT * FROM #tmp_active_users; DROP TABLE IF EXISTS #tmp_active_users; -- Global temp table (cross-session): CREATE TABLE ##shared_work ( val INT ); -- Table variable (alternative, no DDL needed): DECLARE @results TABLE (id INT, val NVARCHAR(100)); INSERT INTO @results VALUES (1,'a');

Oracle

Oracle uses Global Temporary Tables (GTTs): the table structure is permanent (visible in all_tables) but each session's data is private. Data lifetime is either ON COMMIT DELETE ROWS (transaction-scoped, default) or ON COMMIT PRESERVE ROWS (session-scoped). GTTs are created once and reused across sessions — no CREATE per-session. Oracle 18c added private temporary tables (CREATE PRIVATE TEMPORARY TABLE) which exist only for the session duration like other engines.

-- Global temporary table (structure persists, data is private): CREATE GLOBAL TEMPORARY TABLE tmp_active_users ( id NUMBER, username VARCHAR2(100) ) ON COMMIT PRESERVE ROWS; -- session-scoped data -- Transaction-scoped data: CREATE GLOBAL TEMPORARY TABLE tmp_batch ( val NUMBER ) ON COMMIT DELETE ROWS; -- default -- Private temporary table (18c+, session-scoped structure): CREATE PRIVATE TEMPORARY TABLE ora$ptt_work ( id NUMBER ) ON COMMIT DROP DEFINITION; INSERT INTO tmp_active_users SELECT id, username FROM users WHERE active=1; SELECT * FROM tmp_active_users;

SQLite

CREATE TEMPORARY TABLE (or TEMP TABLE) creates a table in the temp database, which is session-scoped and stored in a separate temp file (or in memory). Temp tables are not visible to other database connections. Dropped automatically when the connection closes.

CREATE TEMPORARY TABLE tmp_active_users ( id INTEGER, username TEXT ); INSERT INTO tmp_active_users SELECT id, username FROM users WHERE active = 1; SELECT * FROM tmp_active_users; -- Explicit drop: DROP TABLE IF EXISTS tmp_active_users; -- In-memory temp table (default connection is :memory:): -- All tables are implicitly temp when connection is :memory: