SQL Temporary Tables: Scope, Lifetime and Practical Examples

Run this setup once in a new practice database, before the examples. It uses four customers and six sales. Ana has two sales, Ben has one, Cara has two, and Dev has none.

Sale 106 has no customer link; sale 104 has an unknown amount. These deliberate test cases expose missing information and unmatched relationships.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(40) NOT NULL,
  city VARCHAR(40),
  manager_id INTEGER
);
CREATE TABLE sales (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  amount DECIMAL(10,2),
  status VARCHAR(20) NOT NULL,
  created_on DATE NOT NULL
);
INSERT INTO customers (id,name,city,manager_id) VALUES
  (1,'Ana','Leeds',NULL),(2,'Ben','Leeds',1),
  (3,'Cara','Porto',1),(4,'Dev',NULL,2);
INSERT INTO sales (id,customer_id,amount,status,created_on) VALUES
  (101,1,10,'paid','2026-01-01'),
  (102,1,30,'pending','2026-01-02'),
  (103,2,20,'paid','2026-01-02'),
  (104,3,NULL,'pending','2026-02-01'),
  (105,3,20,'paid','2026-02-02'),
  (106,NULL,5,'paid','2026-02-03');

An amount of zero would be a known value. The NULL amount here means the value is unknown. A customer with no matching sale differs from a sale whose customer_id is NULL.

The primary keys identify individual records even when amounts or cities repeat. Dates use year-month-day format. The examples specify sorting whenever order matters; insertion order alone does not guarantee display order.

A temporary table is a real table-like object that can hold rows, be queried repeatedly and often be indexed. It can stage a filtered sales subset for several calculations without repeating the preparation query. Its lifetime is limited according to the engine’s rules, usually to a connection or a transaction option. The rows are a snapshot of what was inserted, not a live view that automatically incorporates later source changes.

PostgreSQL uses CREATE TEMP TABLE and can specify ON COMMIT behaviour. MySQL uses CREATE TEMPORARY TABLE. SQLite uses CREATE TEMP TABLE in the connection’s temporary database. SQL Server commonly prefixes a local temporary table name with a single hash sign. A global hash-prefixed table has different visibility rules and should not be used casually as if it were private to one session. A normal name without the temporary feature remains a permanent table even if an application plans to delete it later.

Connection scope matters in applications with pools. Creating a temporary object through one connection and reading it through another can produce a missing-table error or unintended behaviour. Ensure the entire workflow uses the intended connection and cleans up according to the engine’s lifetime rules. A pooled connection can outlive one request, so do not assume the end of an HTTP response always closes the database session or removes every session object.

Temporary data has costs: creation, insertion, storage and possible statistics or indexing work. A CTE or derived table can be simpler when the intermediate result is used only once. A temporary table can be useful when the same staged result is reused, must have deliberate indexes, or helps divide a complex transformation into observable steps. Inspect row counts and totals after staging to catch accidental duplicate joins or incomplete filters before later reports consume the intermediate data.

The shown CREATE TEMP TABLE spelling is suitable for PostgreSQL and SQLite. Use CREATE TEMPORARY TABLE in MySQL, and a local #paid_stage table with CREATE TABLE in SQL Server. PostgreSQL ON COMMIT options and SQL Server local/global visibility are separate features, not portable lifetime declarations.

Check the engine-specific syntax and version requirements in PostgreSQL temporary tables, SQL Server temporary tables.

On the same PostgreSQL or SQLite connection, query the paid_stage table to find its smallest and largest known amount.

Try the task before reading its worked solution. The supplied rows make the expected answer checkable by hand.

SELECT MIN(amount) AS smallest,MAX(amount) AS largest FROM paid_stage;

smallest | largest
5 | 20

The staged values are ten, twenty, twenty and five. The pending thirty is not in the stage, so its maximum is twenty rather than the full sales table’s thirty. This confirms that subsequent queries read the stored subset, not the live source definition.

Query paid_stage repeatedly to demonstrate statement-to-statement reuse on one connection. In a separate connection, check whether the local temporary object is visible; it should not be assumed shared. Change source data in a disposable setup and verify the staged rows stay as inserted. Rebuild or refresh the stage deliberately before calling it a current report.