CTE vs Subquery vs Temp Table
Trade-offs in materialization, reuse, and optimizer behavior.
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;All lessons in this course
- Writing Your First CTE
- Chaining Multiple CTEs
- CTE vs Subquery vs Temp Table
- Refactoring Nested Queries Into CTEs