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.
CREATE TEMP TABLE big_orders AS SELECT * FROM orders WHERE shipping > 5;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
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 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 TABLEfrees space sooner in long sessions. - Permissions: you usually need the right to create temporary objects, which is granted separately.
Related
CREATE TABLE AS,CREATE TABLE,WITH(CTE),DROP TABLE- Lessons: Create table, CTEs