CTE vs Temporary Table: What is the Difference?
Understand how a CTE differs from a temporary table in scope, storage, and indexing, and when to choose each in SQL.
Expected Interview Answer
A CTE is a named result set scoped to a single statement with no storage or indexing of its own, while a temporary table is a real, physically stored table that persists for the session or transaction, can be indexed, and can be reused across multiple separate statements.
Because a CTE disappears the moment its statement finishes, it cannot be indexed, cannot have statistics gathered on it, and must be recomputed from scratch every time it is referenced in a fresh statement. A temporary table, by contrast, is created with CREATE TEMPORARY TABLE (or similar), gets written to disk or a temp workspace, can have its own indexes added, and remains available for any subsequent statement in the same session until it is dropped. This makes temporary tables the better choice when the same expensive intermediate result needs to be queried repeatedly across multiple statements or needs indexing for performance, while CTEs are the better choice for organizing logic within a single, self-contained statement.
- CTEs need no cleanup and add no schema objects
- Temporary tables can be indexed for repeated, faster lookups
- Temporary tables persist across multiple statements in a session
- CTEs keep single-statement logic self-contained and easy to review
AI Mentor Explanation
Think of a CTE like a quick chalk mark a fielding coach draws on the pitch just to explain one drill, which fades away the moment that drill ends. A temporary table is more like setting up an actual practice net with markers and cones that stays assembled for the whole training session, usable across many different drills before it is finally packed away. The chalk mark needs no setup or teardown but only serves one explanation, while the practice net takes real setup but can be reused and even reorganized (indexed) across many drills.
Step-by-Step Explanation
Step 1
Assess the reuse scope
Decide whether the intermediate result is needed only within one statement or across several statements in a session.
Step 2
Choose CTE for single-statement logic
Use WITH to name and organize logic that only the immediately following statement will consume.
Step 3
Choose a temporary table for cross-statement reuse
Use CREATE TEMPORARY TABLE when multiple later statements need to query the same intermediate result.
Step 4
Add indexes if the temp table needs speed
Since a temporary table is a real table, create indexes on it if it will be queried or joined repeatedly.
What Interviewer Expects
- Clear statement that a CTE has no storage or indexing and lives for one statement only
- Clear statement that a temporary table is physically stored and persists across statements
- Correct guidance on when to pick each option
- Awareness that a temporary table can be indexed but a plain CTE cannot
Common Mistakes
- Claiming a CTE and a temporary table are functionally interchangeable
- Forgetting that a temporary table needs explicit creation and cleanup (or session-scoped auto-drop)
- Assuming a CTE can be indexed like a temporary table
- Not considering cross-statement reuse when choosing between the two
Best Answer (HR Friendly)
“A CTE is just a named piece of a single query that disappears right after that query runs, with no real storage behind it. A temporary table is an actual table that gets created, can be indexed, and sticks around for the rest of my session, so I would reach for a temp table when several separate queries need to reuse the same expensive intermediate result, and a CTE when I just need to organize logic inside one query.”
Code Example
-- CTE: scoped to this one statement only
WITH big_spenders AS (
SELECT customer_id, SUM(total) AS spend
FROM Orders
GROUP BY customer_id
HAVING SUM(total) > 5000
)
SELECT * FROM big_spenders ORDER BY spend DESC;
-- Temporary table: persists across multiple statements, can be indexed
CREATE TEMPORARY TABLE big_spenders_tmp AS
SELECT customer_id, SUM(total) AS spend
FROM Orders
GROUP BY customer_id
HAVING SUM(total) > 5000;
CREATE INDEX idx_tmp_spend ON big_spenders_tmp (spend);
SELECT * FROM big_spenders_tmp WHERE spend > 10000;
SELECT COUNT(*) FROM big_spenders_tmp;
-- big_spenders_tmp remains available for further statements in this sessionFollow-up Questions
- Can you add an index directly to a CTE the way you can to a temporary table?
- How does a temporary table differ from a table variable in engines that support both?
- When would you choose a materialized view instead of either a CTE or a temporary table?
- How does temporary table lifetime differ between a session-scoped and a transaction-scoped temp table?
MCQ Practice
1. Which of these can be indexed directly?
A temporary table is a real, physically stored table and can have indexes created on it, unlike a plain CTE.
2. How long does a CTE persist compared to a temporary table?
A CTE is scoped to one statement, while a temporary table remains available for later statements until dropped or the session ends.
3. When is a temporary table generally the better choice over a CTE?
Temporary tables shine when an intermediate result needs to be reused, indexed, or queried across multiple statements in a session.
Flash Cards
Does a CTE have physical storage? — No, a CTE has no storage of its own and cannot be indexed.
Does a temporary table have physical storage? — Yes, it is a real table written to disk or temp space and can be indexed.
Which persists across multiple statements? — A temporary table; a CTE only lives for the single statement that defines it.
When should you prefer a temp table over a CTE? — When the intermediate result needs reuse or indexing across several separate statements.