CTEs and Temporary Tables: Scope, Lifetime, and Reuse

“Create a temporary table with the WITH clause” appears in a great deal of SQL teaching material, and it merges two different things. A WITH clause names a query step that exists for the length of one statement. A temporary table is a real table that holds rows, in DuckDB lasts until it is dropped or its connection closes. Choosing between them is a question about three properties: how long the name exists, whether rows are stored, and who else can see it.

The examples run in DuckDB 1.5.5 against a small rides table, and the recorded output follows each statement as a comment. They assume the SELECT, GROUP BY, and JOIN material from SQL from Zero to Working. Use a new, empty file-backed practice database, such as duckdb cte_practice.duckdb in the DuckDB CLI, and run statements individually in the order shown on one connection. Two statements intentionally fail; continue after those expected errors. Do not submit a whole mixed-error block as one API call. A file-backed database is needed for the reconnection claims below: a fresh unnamed in-memory connection starts a different database. The comments show selected result sets, not every client status message.

CREATE TABLE rides (ride_id INTEGER, started_at TIMESTAMP, minutes INTEGER, station VARCHAR);

INSERT INTO rides VALUES
    (1, '2024-03-01 08:10',  75, 'Harbor'), (2, '2024-03-01 09:05',  20, 'Harbor'),
    (3, '2024-03-02 07:40',  90, 'Mill'),   (4, '2024-03-02 18:20',  60, 'Mill'),
    (5, '2024-03-03 12:00',  15, 'Depot'),  (6, '2024-03-03 13:30', 120, 'Depot'),
    (7, '2024-03-04 08:00',  45, 'Harbor'), (8, '2024-03-04 21:15',  65, 'Mill');

SELECT count(*) AS rides, sum(CASE WHEN minutes >= 60 THEN 1 ELSE 0 END) AS long_rides
FROM   rides;
-- rides | long_rides
-- 8 | 5

What a WITH clause actually creates

A common table expression gives a name to a query inside one statement. The name behaves like a table for the rest of that statement and then stops existing. It does not create a separately addressable table for later statements. The engine may still store intermediate rows internally through materialization; name scope and physical storage are different questions.

WITH long_rides AS (SELECT * FROM rides WHERE minutes >= 60)
SELECT count(*) AS long_rides, round(avg(minutes), 1) AS avg_minutes
FROM   long_rides;
-- long_rides | avg_minutes
-- 5 | 82.0

SELECT count(*) FROM long_rides;
-- Catalog Error: Table with name long_rides does not exist!

The second statement fails because the name went out of scope the moment the first statement finished. That error is the difference between a CTE and a temporary table, stated by the engine. If you need the same filtered set in a second statement, a CTE cannot give it to you; you have to repeat the definition or store the rows.

Naming the steps of one statement

Inside one statement, CTEs earn their place. Several of them can be chained, each reading the previous one, so a transformation becomes an ordered list instead of nested parentheses. Define dependencies before the CTEs that use them, as here. This expresses logical dependencies, not a fixed physical execution order; recursive references have separate engine rules.

WITH long_rides AS (SELECT * FROM rides WHERE minutes >= 60),
     by_station AS (
         SELECT station, count(*) AS rides, round(avg(minutes), 1) AS avg_minutes
         FROM   long_rides
         GROUP  BY station
     )
SELECT * FROM by_station ORDER BY station;
-- station | rides | avg_minutes
-- Depot | 1 | 120.0
-- Harbor | 1 | 75.0
-- Mill | 3 | 71.7

One CTE can also be referenced more than once in the same statement, which removes the copied WHERE clause that the original advice was trying to avoid.

WITH long_rides AS (SELECT * FROM rides WHERE minutes >= 60)
SELECT (SELECT count(*) FROM long_rides)                AS long_rides,
       (SELECT count(DISTINCT station) FROM long_rides) AS stations;
-- long_rides | stations
-- 5 | 3

Writing the filter once means one place to change and one condition to review. That is a readability and maintenance benefit, and it is worth claiming. It is not, by itself, a performance claim.

Whether the engine stores the result

Referencing a CTE twice raises a fair question: is the filter computed once or twice? That is the engine’s decision, not the syntax’s. Materialization means computing the CTE once into a temporary result; inlining means substituting its query at each reference. Both are valid, and which one you get depends on the product and version.

WITH long_rides AS MATERIALIZED (SELECT * FROM rides WHERE minutes >= 60)
SELECT count(*) AS long_rides FROM long_rides;
-- long_rides
-- 5

WITH long_rides AS NOT MATERIALIZED (SELECT * FROM rides WHERE minutes >= 60)
SELECT count(*) AS long_rides FROM long_rides;
-- long_rides
-- 5

The hints are accepted and the answer is identical, which is the point: they ask for a different execution strategy, not a different result. PostgreSQL documents the same two keywords and, from version 12, normally folds a once-referenced, nonrecursive, side-effect-free SELECT CTE into its parent query. Recursive or volatile CTEs are not covered by that default. The equal counts above verify results, not that the plans differ. Treat any statement about which plan you will get as something to check with your engine’s plan output, on your data, rather than as a property of the WITH keyword.

A temporary table lives for the session

When the same subset is needed by several statements, a temporary table is the tool the original advice was reaching for. CREATE TEMP TABLE ... AS SELECT runs the query once and stores the rows under a name that later statements in the same session can use.

CREATE TEMP TABLE long_rides_tmp AS SELECT * FROM rides WHERE minutes >= 60;
-- Count
-- 5

SELECT count(*) AS rows_in_temp, round(avg(minutes), 1) AS avg_minutes
FROM   long_rides_tmp;
-- rows_in_temp | avg_minutes
-- 5 | 82.0

SELECT table_name, CAST(temporary AS VARCHAR) AS temporary FROM duckdb_tables() ORDER BY table_name;
-- table_name | temporary
-- long_rides_tmp | true
-- rides | false

The catalog now lists two tables and marks which one is temporary. In this file-backed DuckDB database, after the statements commit, a new connection to the same database sees rides and not long_rides_tmp, and reconnecting means running the creation statement again. The rows occupy memory or storage until the table is dropped or the connection closes, which is a real cost when the subset is large.

A stored result goes stale

Storing rows buys reuse and creates a second copy of the truth. Once the base table changes, the temporary table still holds what the query returned when it ran.

INSERT INTO rides VALUES (9, '2024-03-05 07:00', 180, 'Depot');

SELECT (SELECT count(*) FROM long_rides_tmp)            AS temp_snapshot,
       (SELECT count(*) FROM rides WHERE minutes >= 60) AS live_count;
-- temp_snapshot | live_count
-- 5 | 6

Nothing failed here, which is what makes it dangerous: a report built on long_rides_tmp after this insert is quietly one ride short. A view takes the other trade-off. It stores the query text rather than the rows, so each read uses the data visible under that query’s transaction snapshot. Ordinary data changes do not require recreating the view; schema changes or a changed definition may. A view is not a promise of seeing every concurrent commit immediately.

CREATE VIEW long_rides_v AS SELECT * FROM rides WHERE minutes >= 60;

SELECT count(*) AS rows_in_view FROM long_rides_v;
-- rows_in_view
-- 6

Freshness is not automatically what you want. A month-end report may need exactly the snapshot as it stood when the numbers were signed off, and then the stored copy is the feature, not the defect. State which one your task needs before choosing. A signed-off result that must survive disconnection belongs in a retained table or export with an as-of time and provenance; a session-only table is not an archive.

Choosing among four options

The table describes local temporary tables and ordinary persistent views in a file-backed database; product-specific exceptions follow below. Stored tables can be updated explicitly—the “No” means that the defining SELECT is not rerun automatically. Read the table as four answers to “who needs this result, and for how long.” Steps inside one query are a CTE. A subset reused across statements in your own session is a temporary table. A definition your colleagues should reuse is a view. A result that must survive disconnection, or that is expensive enough to compute once per day, is a permanent table, usually written by a scheduled job rather than by hand.

A recursive CTE builds rows that do not exist

One job a temporary table cannot do more simply is generating a scaffold. A recursive CTE starts from an anchor row and repeatedly applies its own query until the stopping condition fails. Joining rides to a generated calendar shows days with no rides as zero instead of dropping them.

WITH RECURSIVE day_series(day) AS (
    SELECT DATE '2024-03-01'
    UNION ALL
    SELECT day + INTERVAL 1 DAY FROM day_series WHERE day < DATE '2024-03-06'
)
SELECT d.day, count(r.ride_id) AS rides
FROM   day_series d
LEFT   JOIN rides r
       ON  r.started_at >= d.day
       AND r.started_at <  d.day + INTERVAL 1 DAY
GROUP  BY d.day
ORDER  BY d.day;
-- day | rides
-- 2024-03-01 | 2
-- 2024-03-02 | 2
-- 2024-03-03 | 2
-- 2024-03-04 | 2
-- 2024-03-05 | 1
-- 2024-03-06 | 0

Every row of the calendar appears, and count(r.ride_id) counts matched rides rather than joined rows, so a day with no ride would read 0 instead of 1. The recursive term advances one day and stops after March 6; that final date demonstrates the zero case. A recursive query must eventually produce no new rows. Not every engine has a protective default recursion limit, so use a terminating rule and a configured timeout when appropriate. Zero here means no matching source rows, not proof that data collection was complete. The sample ride IDs are non-null; nullable IDs would make this count understate matched rides.

Engine differences to check before copying syntax

The original material listed SELECT INTO beside WITH and CREATE TABLE as interchangeable ways to make a temporary table. They are neither interchangeable nor universally available.

SELECT * INTO long_rides_copy FROM rides WHERE minutes >= 60;
-- Parser Error: SELECT INTO not supported!

CREATE TABLE long_rides_kept AS SELECT * FROM rides WHERE minutes >= 60;
-- Count
-- 6

SELECT table_name, CAST(temporary AS VARCHAR) AS temporary FROM duckdb_tables() ORDER BY table_name;
-- table_name | temporary
-- long_rides_kept | false
-- long_rides_tmp | true
-- rides | false

In this engine SELECT INTO is a parser error, while CREATE TABLE AS produces a permanent table: the flag says false, and committed rows survive reconnection to this file-backed database. In an unnamed in-memory database, a non-temporary table is still lost when that database instance ends. Four differences are worth checking in your own product’s documentation rather than assumed: whether SELECT INTO exists at all, how a temporary table is named and scoped, whether temporary tables can be dropped at the end of a transaction, and whether you have the privilege to create anything.

As documented rather than executed here: PostgreSQL supports CREATE TEMP TABLE with an ON COMMIT option, and plain SQL SELECT INTO creates a table; PostgreSQL’s procedural PL/pgSQL uses INTO for variable assignment instead. SQL Server marks temporary tables with #name for the session and ##name for all sessions. BigQuery documents temporary tables for multi-statement queries and sessions; script-created temporary data can remain for up to 24 hours after execution, though that is not a normal shared-table lifetime. Check the version you actually run against; these rules change between releases.

Cleanup, names, and privileges

A temporary table disappears with its session. A permanent one does not, and the working tables nobody dropped are how a shared schema fills with rides_tmp2_final. Drop what you created as soon as the step is done, and keep a name that says what the rows are and who made them.

DROP VIEW  long_rides_v;
DROP TABLE long_rides_kept;
DROP TABLE long_rides_tmp;

SELECT table_name FROM duckdb_tables() ORDER BY table_name;
-- table_name
-- rides

Creating and dropping objects are schema changes, so both may require privileges you do not have on a production database, and both may be logged. On a shared system, prefer a CTE inside one statement, then a temporary table in your own session, and reach for a permanent table only when someone else needs the result. When several such statements must succeed or fail together, wrap them in a transaction, and remember that in some engines a data definition statement ends it.

Practice

1. A colleague writes one statement defining WITH long_rides AS (...), then a second statement reading long_rides, and reports that “the temporary table disappeared.” Explain what happened and give two ways to make the second statement work.

CREATE TEMP TABLE long_rides_tmp AS SELECT * FROM rides WHERE minutes >= 60;
SELECT count(*) AS long_rides FROM long_rides_tmp;
-- long_rides
-- 6
DROP TABLE long_rides_tmp;

2. A dashboard reads a temporary table created at the start of a long session. Rides keep arriving during the day. What will the dashboard show, and which two options fix it? State what each option costs.

3. You need daily ride counts for a six-day window, including any day with no rides, and a teammate proposes filling the gaps in a spreadsheet afterwards. Write the SQL approach instead, and say which part guarantees the missing day appears.