CTE vs temp table in SQL — when to use which

The short answer

A CTE (WITH clause) is a named subquery that lives inside a single SQL statement. A temp table (CREATE TEMP TABLE) is a real table that lives for the duration of your session. Rule of thumb: use a CTE when you want to decompose one complex query into readable steps, and reach for a temp table when the same intermediate result is reused across several statements or when the CTE version is too slow because the planner keeps recomputing it.

That's the headline. The interesting part — and the part interviewers actually probe — is the second-order stuff: how Postgres materializes CTEs since version 12, why your CTE got scanned three times in production, and how a single CREATE INDEX on a temp table cut a 40-second pipeline down to 4. Picture a typical Monday morning at Stripe or Snowflake: your PM wants daily revenue, growth rates, and a cohort breakdown by lunch. You can solve it with one big CTE chain, or stage results into temp tables and join them. Both work. One is faster to write; the other is faster to run.

Quick comparison table

The rest of this post explains why each row looks the way it does, with worked SQL.

When CTE is the right tool

Decomposing a complex query

WITH monthly_revenue AS (
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        SUM(amount) AS revenue
    FROM orders
    GROUP BY DATE_TRUNC('month', order_date)
),
growth AS (
    SELECT
        month,
        revenue,
        LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
        ROUND(
            (revenue - LAG(revenue) OVER (ORDER BY month))::NUMERIC
            / LAG(revenue) OVER (ORDER BY month) * 100, 1
        ) AS growth_pct
    FROM monthly_revenue
)
SELECT * FROM growth
WHERE growth_pct IS NOT NULL
ORDER BY month;

A CTE breaks a query into logical blocks, each a named step you can reason about in isolation. It reads much better than the same logic crammed into nested subqueries, and reviewers can comment line-by-line on the CTE definitions during code review.

When CTE wins

Pick a CTE when the intermediate result is consumed only inside one statement, when readability is the top priority, when you need recursion (recursive CTEs have no temp-table equivalent), when you're doing ad hoc exploration and don't want to manage CREATE/DROP, or when you don't have DDL privileges on the schema — a common situation for analysts hitting a read-replica at Notion or Linear.

CTEs are also great for self-documenting queries. A name like active_paying_users_last_30d tells the next reader what the block does without forcing them to parse 40 lines of joins.

When a temp table wins

One intermediate result, several queries

-- Step 1: build the cohort
CREATE TEMP TABLE active_users AS
SELECT
    user_id,
    COUNT(*) AS sessions,
    MAX(event_date) AS last_active
FROM user_events
WHERE event_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY user_id
HAVING COUNT(*) >= 5;

-- Step 2: revenue per active user
SELECT
    a.user_id,
    a.sessions,
    SUM(o.amount) AS revenue
FROM active_users a
JOIN orders o ON a.user_id = o.user_id
GROUP BY a.user_id, a.sessions;

-- Step 3: segment breakdown
SELECT segment, COUNT(*) AS cnt
FROM users u
JOIN active_users a ON u.user_id = a.user_id
GROUP BY segment;

A CTE can't span statements — its scope ends at the semicolon. A temp table is computed once, persists for the session, and gets reused for free. On a 50-million-row event table, that's the difference between a 3-minute pipeline and a 12-minute one.

Performance optimization

-- Slow path: CTE may be re-evaluated each reference
WITH big_cte AS (
    SELECT ... -- heavy query WITH 5 joins
)
SELECT * FROM big_cte WHERE ...
UNION ALL
SELECT * FROM big_cte WHERE ...;

-- Faster path: temp table + targeted index
CREATE TEMP TABLE big_result AS
SELECT ...; -- same heavy query, runs once

CREATE INDEX idx_big_result ON big_result(user_id);

SELECT * FROM big_result WHERE ...
UNION ALL
SELECT * FROM big_result WHERE ...;

When the temp table wins

Reach for a temp table when the intermediate is consumed by several follow-up statements, when you need an index to make downstream joins fast, when the dataset is large enough that you want predictable materialization (no surprises from the planner inlining things), when you want to debug by selecting from the intermediate directly, and when you want ANALYZE to give the optimizer real statistics so it picks a good plan downstream.

Materialization across engines

The key difference is how the engine treats your CTE.

PostgreSQL 12+: CTEs are inlined by default. The planner folds the CTE into the outer query and optimizes the whole thing as one statement. Before Postgres 12, CTEs were always materialized — an "optimization fence" you could exploit, but also a footgun for people migrating from MySQL or SQL Server.

-- Force materialization (Postgres 12+)
WITH cte AS MATERIALIZED (
    SELECT ... -- heavy query you only want to run once
)
SELECT * FROM cte WHERE ...;

-- Force inlining (Postgres 12+)
WITH cte AS NOT MATERIALIZED (
    SELECT ... -- light query the planner should fold
)
SELECT * FROM cte WHERE ...;

MySQL: CTEs may be materialized or inlined — the optimizer chooses based on cost.

SQL Server: CTEs are typically inlined and never persisted; for a materialization fence, use a temp table or table variable.

Snowflake and BigQuery: CTEs are inlined by default. Both engines fold CTEs into the outer plan aggressively, so the performance gap between a clean CTE chain and a temp-table version is often smaller than you'd expect — but not zero on very large scans.

Temp tables: always materialized. Data is written to disk (or memory) and you pay the I/O cost once.

Worked example: when CTE gets slow

-- CTE: subquery executes twice if it's not materialized
WITH user_stats AS (
    SELECT user_id, SUM(amount) AS total
    FROM orders
    GROUP BY user_id
)
SELECT * FROM user_stats WHERE total > 1000
UNION ALL
SELECT * FROM user_stats WHERE total BETWEEN 500 AND 1000;

If your engine inlines this CTE, you scan orders twice — once per UNION ALL branch. With AS MATERIALIZED (or a temp table) you scan it once and reuse the cached result. On a 200-million-row orders table at DoorDash or Uber, that's the difference between a query you run during a meeting and one you start before lunch.

-- Temp table: one pass, then reuse
CREATE TEMP TABLE user_stats AS
SELECT user_id, SUM(amount) AS total
FROM orders
GROUP BY user_id;

SELECT * FROM user_stats WHERE total > 1000
UNION ALL
SELECT * FROM user_stats WHERE total BETWEEN 500 AND 1000;

Add CREATE INDEX idx_user_stats_total ON user_stats(total); and the range scans get even cheaper. You can't do that with a CTE.

Subquery vs CTE vs temp table

-- Subquery: compact, but ugly when nested
SELECT * FROM (
    SELECT user_id, SUM(amount) AS total
    FROM orders GROUP BY user_id
) sub WHERE total > 1000;

-- CTE: readable, single statement
WITH user_totals AS (
    SELECT user_id, SUM(amount) AS total
    FROM orders GROUP BY user_id
)
SELECT * FROM user_totals WHERE total > 1000;

-- Temp table: reusable across statements
CREATE TEMP TABLE user_totals AS
SELECT user_id, SUM(amount) AS total
FROM orders GROUP BY user_id;

SELECT * FROM user_totals WHERE total > 1000;
SELECT * FROM user_totals WHERE total BETWEEN 500 AND 1000;

Rule of thumb: subquery for trivial cases, CTE for one complex statement, temp table for a multi-step pipeline you'll run more than once.

Common pitfalls

The biggest trap is treating CTEs as free. They aren't. If your CTE is referenced multiple times in one statement and the planner inlines rather than materializes, you'll re-execute the underlying scan once per reference. On a heavy join this turns a 10-second query into a 40-second one — the fix is AS MATERIALIZED on Postgres 12+ or a temp table. Always check EXPLAIN ANALYZE when a clean refactor to CTEs suddenly runs slower than the original messy version.

The opposite mistake is reaching for a temp table when a CTE would do. If the intermediate is used exactly once, you're paying full materialization cost for nothing — disk writes, no index, no statistics, just churn. Plus you've added two extra statements your colleagues need to understand. Keep it as a CTE and let the planner do its job.

Another common bug is forgetting that temp tables persist for the whole session. If you re-run a script starting with CREATE TEMP TABLE foo AS ..., the second run fails with "relation already exists". Fix: DROP TABLE IF EXISTS foo; at the top, or CREATE TEMP TABLE IF NOT EXISTS foo.

Missing indexes on temp tables is a quiet performance killer. Analysts materialize a temp table, immediately join it back to a 100-million-row events table on user_id, and watch the query run for ten minutes. A single CREATE INDEX idx_temp_user ON temp_table(user_id); after materialization usually slashes that to seconds. Add an index when the temp table will be the small side of a hash join.

Last one: ignoring statistics. After loading a temp table on Postgres, run ANALYZE temp_table before joining to anything large. Without statistics the planner assumes default row counts and can pick a catastrophically wrong join order.

Interview questions

How does a CTE differ from a temp table? A CTE is a named subquery that exists only inside a single statement; it usually isn't materialized, has no indexes, and disappears at the semicolon. A temp table is a real table in tempdb (or session memory), persists for the whole session, supports indexes and statistics, and gives you predictable performance at the cost of materialization overhead.

When is a CTE better than a subquery? When the same subquery appears multiple times in one statement, or when nested subqueries make the code unreadable. Also when you need recursion — recursive CTEs have no subquery equivalent.

When should you use a temp table instead of a CTE? When the same intermediate result feeds multiple statements, when you need an index for a downstream join, or when the CTE keeps being re-evaluated. In ETL pipelines that run nightly, temp tables (or proper staging tables) are almost always the right call.

Are CTEs materialized in PostgreSQL? Since Postgres 12 the default is not materialized — the planner inlines the CTE into the outer query. You can force the old behavior with WITH cte AS MATERIALIZED (...). Before Postgres 12, every CTE was an optimization fence and always materialized.

Related reading

• SQL window functions interview questions
• CTE with SQL guide
• CTE SQL cheat sheet

If you want to drill SQL questions like this every day, NAILDD is launching with 500+ SQL problems covering exactly this pattern.

FAQ

CTE vs VIEW — what's the difference?

A CTE exists only inside one statement; a view is a saved query definition reusable from any statement at any time. You don't create a CTE in advance — you just write it where you need it. A view requires CREATE VIEW and persists until you drop it. Views also play nicely with permissions: you can grant read access on a view without exposing the underlying tables, which matters at Airbnb or Stripe where data access is tightly scoped.

Can you create an index on a CTE?

No. A CTE is a virtual construct that doesn't get stored anywhere permanent — there's nothing to index. If you need an index on intermediate data, materialize it into a temp table and create the index there. Some engines let you hint at materialization (Postgres AS MATERIALIZED), but even then you can't attach an index.

Which is faster — CTE or subquery?

In most modern engines, they're equivalent. The optimizer typically rewrites a CTE into the same plan it would produce for an inline subquery. The choice is about readability — until you reference the CTE multiple times in one statement, at which point the materialization story (and engine version) starts to matter.

Do temp tables survive a transaction rollback?

It depends on the engine. In Postgres, temp tables persist for the session by default, surviving rollbacks inside that session. To tie a temp table to a transaction, use CREATE TEMP TABLE foo ON COMMIT DROP. In SQL Server, behavior varies between local (#foo) and global (##foo) temp tables. Always check the engine docs before assuming cleanup happens automatically.

Is WITH RECURSIVE faster than a self-join loop?

Usually yes, because the engine pipelines rows through the recursive step instead of materializing the full result of each iteration. But recursive CTEs have a recursion-depth cap and can blow up memory if your termination condition is weak. For huge hierarchical traversals (org charts, comment threads), test both approaches with EXPLAIN ANALYZE on representative data before committing.