Temporary Tables

Sometimes it is easier to create temporary tables than to use a CTE or subquery. If the data is not expected to change at all or not very often, it is faster to use a table to join and query by rather than a normal SELECT query.

Temporary Tables in SQLite

In SQLite, temporary tables are created using CREATE TEMP TABLE. These tables are stored in memory and are automatically deleted when the connection closes.

-- Create a temporary table
CREATE TEMP TABLE SalesAgg AS
SELECT Region, Product, SUM(Amount) AS TotalSales
FROM Sales
GROUP BY Region, Product;

-- Query the temporary table
SELECT * FROM SalesAgg WHERE Region = 'North';

-- Another query on the same temp table
SELECT * FROM SalesAgg WHERE TotalSales > 100000;

Temporary tables are cheap to create and very cheap to query once the data is materialized.

When to Use Temporary Tables

Temporary tables are particularly useful when:

• Multiple queries need the same aggregated data - Materialize once, query many times
• Complex calculations are reused - Avoid recalculating expensive operations
• Data needs to be staged - Transform data in steps
• Breaking down complex queries - Improve readability and debugging

Temporary Tables vs CTEs

CTEs are recomputed each time they are referenced, while temporary tables are materialized once.

-- CTE: Recalculated for each query
WITH SalesAgg AS (
  SELECT Region, Product, SUM(Amount) AS TotalSales
  FROM Sales
  GROUP BY Region, Product
)
SELECT * FROM SalesAgg WHERE Region = 'North';

WITH SalesAgg AS (
  SELECT Region, Product, SUM(Amount) AS TotalSales
  FROM Sales
  GROUP BY Region, Product
)
SELECT * FROM SalesAgg WHERE TotalSales > 100000;

-- Temporary table: Materialized once (SQLite example)
CREATE TEMP TABLE SalesAgg AS
SELECT Region, Product, SUM(Amount) AS TotalSales
FROM Sales
GROUP BY Region, Product;

SELECT * FROM SalesAgg WHERE Region = 'North';
SELECT * FROM SalesAgg WHERE TotalSales > 100000;

Temporary Tables by Database

ℹ️ The syntax for creating temporary tables differs based on the SQL flavor you are using. Here are examples for the most common databases:

Database	Syntax	Example
SQLite	CREATE TEMP TABLE	CREATE TEMP TABLE SalesAgg AS SELECT ...
MySQL	CREATE TEMP TABLE	CREATE TEMP TABLE SalesAgg AS SELECT ...
SQL Server	#TableName	SELECT * INTO #SalesAgg FROM Sales
PostgreSQL	CREATE TEMP TABLE	CREATE TEMP TABLE SalesAgg AS SELECT ...
Oracle	GLOBAL TEMPORARY TABLE	CREATE GLOBAL TEMPORARY TABLE SalesAgg (...)

Examples by Database

SQLite / MySQL / PostgreSQL:

CREATE TEMP TABLE SalesAgg AS
SELECT Region, Product, SUM(Amount) AS TotalSales
FROM Sales
GROUP BY Region, Product;

SQL Server:

SELECT Region, Product, SUM(Amount) AS TotalSales
INTO #SalesAgg
FROM Sales
GROUP BY Region, Product;

Oracle:

CREATE GLOBAL TEMPORARY TABLE SalesAgg (
    Region VARCHAR2(50),
    Product VARCHAR2(100),
    TotalSales NUMBER
) ON COMMIT PRESERVE ROWS;

INSERT INTO SalesAgg (Region, Product, TotalSales)
SELECT Region, Product, SUM(Amount) FROM Sales GROUP BY Region, Product;

💡 Use temporary tables when you have multiple queries referencing the same intermediate result to avoid recalculating it each time.