CTEs vs Subqueries vs Temp Tables — When to Use Each
The Interview Question
What is a CTE? How does it compare to a subquery or a temp table?
Expert Answer
A Common Table Expression (CTE) is a named temporary result set defined with the WITH keyword that exists only for the duration of a single query. It's not materialized — the database may inline it or execute it multiple times. CTEs shine for readability: they let you break complex queries into named, logical steps that read top to bottom. Subqueries (inline queries in FROM, WHERE, or SELECT) do the same thing but nest, making complex queries hard to read when they go multiple levels deep. Temp tables are physically created, store actual data, and persist for the session. Use CTEs when you need readability and the result set is used once in the query. Use temp tables when you reference the same intermediate result multiple times (a CTE referenced twice may be computed twice), when the intermediate result benefits from its own indexes, or when you need to break a complex process across multiple statements. Recursive CTEs are a unique feature — they can traverse hierarchies (org charts, category trees) that no other query structure handles.
Key Points to Hit in Your Answer
- CTE (WITH): readable, single-query scope, may be computed multiple times
- Subquery: inline, can nest deeply, same performance as CTE in most databases
- Temp table: materialized, persists for session, can be indexed
- CTEs are NOT automatically materialized (database dependent — PostgreSQL may, MySQL won't)
- Use temp tables when referencing the same intermediate result multiple times
- Recursive CTEs: unique capability for hierarchical data traversal
- CTEs improve readability; temp tables improve performance for complex pipelines
Code Example
-- CTE: readable step-by-step logic
WITH monthly_sales AS (
SELECT
DATE_TRUNC('month', order_date) AS month,
SUM(amount) AS total
FROM orders
GROUP BY 1
),
growth AS (
SELECT
month,
total,
LAG(total) OVER (ORDER BY month) AS prev_total,
total - LAG(total) OVER (ORDER BY month) AS change
FROM monthly_sales
)
SELECT * FROM growth WHERE change < 0; -- months with decline
-- Recursive CTE: org chart traversal
WITH RECURSIVE org_tree AS (
-- Base case: CEO (no manager)
SELECT id, name, manager_id, 0 AS depth
FROM employees WHERE manager_id IS NULL
UNION ALL
-- Recursive case: each employee's reports
SELECT e.id, e.name, e.manager_id, t.depth + 1
FROM employees e
JOIN org_tree t ON e.manager_id = t.id
)
SELECT * FROM org_tree ORDER BY depth, name;
-- Temp table: reuse intermediate result
CREATE TEMP TABLE active_users AS
SELECT user_id, COUNT(*) AS actions
FROM user_activity
WHERE activity_date > CURRENT_DATE - 30
GROUP BY user_id;
-- Now use it in multiple queries efficiently
SELECT * FROM active_users WHERE actions > 100;
SELECT department, AVG(actions) FROM active_users
JOIN users USING (user_id) GROUP BY department;What Interviewers Are Really Looking For
The key insight is that CTEs are about readability, not performance (in most databases). Knowing that a CTE referenced twice may execute twice shows understanding of execution. Recursive CTEs for hierarchy traversal is a powerful tool that many candidates don't know — mentioning it proactively stands out. The practical rule: if you need it once, CTE; if you need it multiple times, temp table.
Practice This Question with AI Grading
Reading about interview questions is a start — but practicing with real-time AI feedback is how you actually get better. Goliath Prep grades your answers instantly and tells you exactly what you're missing.
Start Practicing Free →