0Pricing
SQL Interview Prep · Lesson

CTE vs Subquery vs Temp Table

Trade-offs in materialization, reuse, and optimizer behavior.

CTE vs Subquery vs Temp Table is a free SQL Interview Prep lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Three Ways to Stage Logic

When a query needs an intermediate result, you have three common tools: a subquery, a CTE, and a temporary table. Interviewers ask you to compare them because the choice signals whether you understand materialization and optimizer behavior.

This lesson builds a decision framework you can recite under pressure.

The Subquery

A subquery is an inline query nested inside another, often in FROM, WHERE, or SELECT. It is part of the same statement and the optimizer sees it as a single unit.

  • No name needed (derived tables do need an alias).
  • Optimizer is free to merge it into the outer query.
  • Gets verbose and hard to read when nested deeply.
SELECT *
FROM (
    SELECT customer_id, SUM(amount) AS total
    FROM orders
    GROUP BY customer_id
) t
WHERE t.total > 1000;

The CTE

A CTE is a named subquery in a WITH block, scoped to one statement. It reads better than a deeply nested subquery and can be referenced multiple times.

  • Named, so the intent is documented.
  • Can be referenced more than once in the same statement.
  • Still scoped to a single statement, then gone.
WITH spend AS (
    SELECT customer_id, SUM(amount) AS total
    FROM orders
    GROUP BY customer_id
)
SELECT *
FROM spend
WHERE total > 1000;

The Temporary Table

A temp table is a real, physical table that lives for the session (or transaction). You populate it with one statement and query it in later, separate statements.

  • Persists across multiple statements in the session.
  • Can be indexed and have statistics gathered.
  • Costs disk I/O and explicit cleanup.
CREATE TEMP TABLE spend AS
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id;

SELECT * FROM spend WHERE total > 1000;

Materialization: The Core Distinction

The key concept interviewers probe is materialization: whether the intermediate result is physically written somewhere.

  • Subqueries and CTEs are usually not materialized; the optimizer often inlines them.
  • A temp table is always materialized to storage.
  • Some databases let you force or block CTE materialization with hints.

Optimizer Fences and the Old Postgres Trap

Historically, PostgreSQL treated every CTE as an optimization fence, materializing it and blocking predicate push-down. Since Postgres 12, simple non-recursive CTEs referenced once are inlined by default, with MATERIALIZED and NOT MATERIALIZED hints to override.

Mentioning this nuance is a strong senior signal.

WITH spend AS NOT MATERIALIZED (
    SELECT customer_id, SUM(amount) AS total
    FROM orders GROUP BY customer_id
)
SELECT * FROM spend WHERE total > 1000;

Reuse Within One Statement

If you reference the same intermediate result several times in one statement, a CTE can be cleaner than repeating a subquery. But beware: an inlined CTE may be recomputed on each reference.

When recomputation is expensive, forcing materialization (or using a temp table) avoids doing the work twice.

Reuse Across Statements

CTEs and subqueries live for one statement only. If you need the same result in several separate queries, the temp table is the right tool.

Typical case: a multi-step ETL or report where you build a staging set once, then run several analyses against it. Indexing the temp table can then accelerate every follow-up query.

Indexing and Statistics

Only a temp table can carry indexes and fresh statistics. For a huge intermediate set joined many times, that can be decisive.

  • CTE/subquery: optimizer estimates from the underlying tables.
  • Temp table: you can ANALYZE it and add indexes tuned to your later joins.

So for large, heavily reused results, a temp table may win on performance despite the extra steps.

The Decision Framework

A crisp answer for the interview:

  • Subquery: one-off, shallow, readability is fine.
  • CTE: improves readability or you reference it a few times in one statement.
  • Temp table: reused across statements, very large, or you need indexes/statistics.

Default to a CTE for clarity; reach for a temp table when materialization or cross-statement reuse genuinely helps.

How to Frame the Trade-off

Avoid absolutes like 'CTEs are always slower.' Say instead: CTEs and subqueries are usually inlined, so they are about readability; a temp table is materialized and worth it when I reuse a large result across statements or need an index.

Acknowledging that behavior is engine-specific (and version-specific in Postgres) shows real depth.

Quick Check

Pick the scenario where a temporary table is the clearly better choice.

Recap: CTE vs Subquery vs Temp Table

The choice hinges on materialization and scope.

  • Subqueries and CTEs: usually inlined, scoped to one statement, chosen for readability.
  • CTEs add naming and intra-statement reuse.
  • Temp tables: always materialized, persist across statements, can be indexed.
  • Postgres 12+ inlines simple CTEs; use MATERIALIZED hints to control it.

Next: refactoring a tangled nested query into clean CTEs.

Frequently asked questions

Is the “CTE vs Subquery vs Temp Table” lesson free?

Yes — the full text of “CTE vs Subquery vs Temp Table” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “CTE vs Subquery vs Temp Table”?

Trade-offs in materialization, reuse, and optimizer behavior. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “CTE vs Subquery vs Temp Table” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Writing Your First CTE
  2. Chaining Multiple CTEs
  3. CTE vs Subquery vs Temp Table
  4. Refactoring Nested Queries Into CTEs
← Back to SQL Interview Prep