Skip to content
Quantoros

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

Related questions