What is a CTE vs a temp table?
SQLLevel: IntermediateSubqueries & CTEs
What is a CTE vs a temp table?
Hints
- Define one output row and the source-table keys.
- Check duplicates, NULLs and the order of filtering and aggregation.
Answer
A CTE exists only within a single query. A temp table persists for the session (or transaction) and can be indexed, analyzed, and queried multiple times separately.
Use a temp table when:
- You need to run multiple separate queries against the intermediate result
- The intermediate result is large and needs an index to be queried efficiently
- You want to build the result incrementally across multiple steps
In PostgreSQL, temp tables also allow ANALYZE so the query planner can make better decisions.
Source and licence: SQL Interview Questions — Arda Kocadoru · MIT