CTE vs Subquery vs Temp Table vs View: When to Use Each

Four Tools, One Underlying Goal

CTEs, subqueries, temp tables, and views all let you build a query on top of an intermediate result — the confusion isn't about what they can do (often the same thing), but about which one fits a given situation best. This guide is a practical decision framework, not just four separate definitions.

Quick Definitions

• Subquery — a query nested inside another, scoped to that single statement
• CTE (Common Table Expression) — a named, temporary result set defined with WITH, scoped to one statement, reusable multiple times within it
• Temp table — a real (or session-scoped in-memory) table that persists across multiple separate statements within a session or transaction
• View — a saved, named query definition stored permanently in the database schema, re-executed every time it's referenced

Subquery vs. CTE

-- Subquery: works, but gets unreadable once nested multiple levels deep
SELECT * FROM (
  SELECT department, AVG(salary) AS avg_sal FROM employees GROUP BY department
) dept_avgs
WHERE avg_sal > (SELECT AVG(salary) FROM employees);

-- CTE: same logic, clearer structure, and the department average is defined once
WITH dept_avgs AS (
  SELECT department, AVG(salary) AS avg_sal FROM employees GROUP BY department
),
company_avg AS (
  SELECT AVG(salary) AS avg_sal FROM employees
)
SELECT * FROM dept_avgs, company_avg
WHERE dept_avgs.avg_sal > company_avg.avg_sal;

Choose a CTE over a subquery when: the same intermediate result is needed more than once in the query, the logic is complex enough that naming each step improves readability, or you need recursion (only CTEs support WITH RECURSIVE — see our recursive CTEs guide). Choose a plain subquery for a single, simple, one-off filter or derived value where naming it wouldn't add clarity.

CTE vs. Temp Table

-- CTE: exists only for the duration of this one statement
WITH high_earners AS (
  SELECT * FROM employees WHERE salary > 100000
)
SELECT department, COUNT(*) FROM high_earners GROUP BY department;

-- Temp table: persists across multiple statements in the same session
CREATE TEMP TABLE high_earners AS
SELECT * FROM employees WHERE salary > 100000;

SELECT department, COUNT(*) FROM high_earners GROUP BY department;
SELECT MAX(salary) FROM high_earners;   -- can reuse it in a completely separate statement

Choose a temp table over a CTE when: the same intermediate result needs to be reused across multiple separate statements (not just multiple references within one query), the intermediate result is large enough that you want to explicitly index it for repeated access, or you're building a multi-step ETL/batch process where breaking work into discrete, inspectable stages matters more than a single elegant query.

Performance note: a CTE is generally optimized as part of the single query it's embedded in (modern PostgreSQL, since version 12, can inline simple CTEs into the main query plan; older versions and some other databases may materialize the CTE first as an "optimization fence"). A temp table is always materialized and can be explicitly indexed — genuinely useful when the same expensive intermediate result is queried repeatedly with different subsequent filters.

CTE/Subquery vs. View

-- View: saved permanently in the schema, reusable across many different future queries
CREATE VIEW active_high_earners AS
SELECT * FROM employees WHERE salary > 100000 AND status = 'active';

-- Now any query, written at any time in the future, can simply reference it
SELECT department, COUNT(*) FROM active_high_earners GROUP BY department;

Choose a view when: the same logic needs to be reused across many different queries, written by different people, over time — not just within one query or one session. A view is essentially a saved, shareable CTE definition, useful for standardizing a business definition (like "what counts as an active high earner") across an entire team or application, so that definition only needs to be updated in one place.

A view adds no storage cost and no precomputation — it's re-executed in full every time it's queried, exactly like inlining the same subquery each time. For a view that's expensive to compute and doesn't need to reflect live data, see materialized views below.

Materialized Views: The Fifth Option

CREATE MATERIALIZED VIEW dept_summary AS
SELECT department, COUNT(*) AS headcount, AVG(salary) AS avg_salary
FROM employees GROUP BY department;

-- Query it like a table — fast, since the result is physically stored
SELECT * FROM dept_summary WHERE avg_salary > 90000;

-- Must be explicitly refreshed to reflect new underlying data
REFRESH MATERIALIZED VIEW dept_summary;

A materialized view physically stores its result (unlike a regular view) and must be refreshed on a schedule or on demand — trading data freshness for read speed. Choose this when a view's logic is expensive to compute and queried very frequently, and slightly stale data is acceptable.

Decision Guide

Situation	Best choice
One-off filter, used once, in one query	Subquery
Multi-step logic, reused a few times, within one query	CTE
Need recursion	CTE (WITH RECURSIVE)
Reused across multiple statements in the same session/ETL job	Temp table
Reused across many different future queries, by different people	View
Expensive to compute, queried very frequently, slight staleness is OK	Materialized view

Common Mistakes

• Using a temp table when a CTE would do — adds unnecessary write overhead and cleanup logic for something that only ever needed to exist within a single query.
• Assuming a CTE is always materialized (or never materialized) — behavior varies by database and version; don't assume performance characteristics without checking your specific engine's documentation or EXPLAIN output.
• Creating a view for logic that's only ever used in one place — adds schema clutter and an extra layer of indirection with no real reuse benefit.
• Forgetting to refresh a materialized view and being surprised by stale data — always be explicit (in code comments or a scheduled job) about the refresh cadence.

Common Interview Questions

1. What's the main practical difference between a CTE and a subquery? A CTE is named and can be referenced multiple times within the same query; a subquery is typically single-use and can become unreadable when nested deeply. CTEs also uniquely support recursion.
2. When would you use a temp table instead of a CTE? When the intermediate result needs to be reused across multiple separate statements, or is large enough to benefit from its own index.
3. What's the difference between a view and a materialized view? A view is just a saved query, re-executed on every reference; a materialized view physically stores its result and must be refreshed, trading freshness for speed.
4. Does using a CTE guarantee better performance than a subquery? No — a CTE's main benefit is readability and reusability within a query; whether it's faster, slower, or identical to an equivalent subquery depends on the specific database and how it optimizes (or materializes) CTEs.

Frequently Asked Questions

Can a CTE reference another CTE defined earlier in the same WITH clause?

Yes — CTEs in the same WITH clause can reference earlier ones (but not later ones), letting you build up multi-step logic cleanly, as shown in the dept_avgs/company_avg example above.

Do views have any performance cost of their own?

A regular view has no storage cost and is logically equivalent to inlining its definition at query time — so its performance is identical to writing the same query directly, not faster and not slower on its own.

Practice Choosing the Right Tool

Knowing when to reach for each of these is real SQL engineering judgment, not just syntax knowledge. Try it hands-on with our SQL practice questions, or work through multi-step data problems in our case studies. For the subquery fundamentals this comparison builds on, see our subqueries guide.