📌 Disclosure: This post contains affiliate links. If you purchase through these links, I may earn a small commission at no extra cost to you. I only recommend resources I genuinely believe will help you.
SQL CTEs and subqueries are two of the most powerful tools in any analyst's toolkit — but knowing when to use each one makes all the difference.
If you've been writing SQL for a while, you've probably heard of CTEs and subqueries — and wondered what the actual difference is. They both let you nest a query inside another query. They often produce the same result. So why does it matter which one you use? This guide breaks it all down clearly, with real examples, a side-by-side comparison, performance insights, and a simple decision guide so you always know which to reach for.
By the end of this article you'll know:
- What CTEs and subqueries are — and how they work
- The key differences between them with real SQL examples
- When to use a CTE vs a subquery (and why)
- Performance considerations for both
- How to use recursive CTEs for hierarchical data
- A quick decision guide you can bookmark
- What Is a Subquery?
- What Is a CTE (Common Table Expression)?
- Side-by-Side: Same Problem, Two Approaches
- Key Differences: CTE vs Subquery
- When to Use a CTE
- When to Use a Subquery
- Performance: Which Is Faster?
- Bonus: Recursive CTEs (What Subqueries Can't Do)
- Quick Decision Guide
- Best Books and Courses to Master SQL
- Frequently Asked Questions
1. What Is a Subquery?
A subquery is a query nested inside another SQL query — wrapped in parentheses. It can appear in the SELECT, FROM, or WHERE clause of the outer query.
Here's a simple example. We want to find all employees who earn more than the company average salary:
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
);
-- The inner query calculates the average salary
-- The outer query filters employees above that average
The inner query runs first, produces a single value (the average salary), and the outer query uses that value to filter results. Clean and simple — for a single-use case.
Subqueries nest one query inside another — powerful for single-use filtering and inline calculations.
2. What Is a CTE (Common Table Expression)?
A CTE (Common Table Expression) is a temporary, named result set that you define at the beginning of a query using the WITH keyword. Think of it as giving your subquery a name and pulling it out of the main query — making everything much easier to read.
Here's the exact same problem solved with a CTE:
SELECT AVG(salary) AS company_avg
FROM employees
)
SELECT e.employee_name, e.salary
FROM employees e
JOIN avg_salary a ON e.salary > a.company_avg;
-- The CTE is defined once with a clear name
-- The main query reads like plain English
The result is identical — but the code is immediately more readable. The CTE is named avg_salary, which tells you exactly what it contains before you even read it.
3. Side-by-Side: Same Problem, Two Approaches
Let's look at a more realistic scenario — finding the top-spending customer per country. This requires multiple steps: aggregate orders, rank by country, then filter top ones. See how differently the two approaches look:
FROM (
SELECT
customer_name, country,
SUM(order_amount) AS total_spend,
RANK() OVER (PARTITION BY country ORDER BY SUM(order_amount) DESC) AS rnk
FROM orders
GROUP BY customer_name, country
) ranked_customers
WHERE rnk = 1;
SELECT
customer_name,
country,
SUM(order_amount) AS total_spend
FROM orders
GROUP BY customer_name, country
),
ranked_customers AS (
SELECT *,
RANK() OVER (PARTITION BY country ORDER BY total_spend DESC) AS rnk
FROM customer_totals
)
SELECT customer_name, country, total_spend
FROM ranked_customers
WHERE rnk = 1;
Both queries produce identical results. But the CTE version is broken into two named logical steps — customer_totals and ranked_customers — making it far easier to read, debug, and maintain. Imagine coming back to this query six months later. Which version would you rather see?
4. Key Differences: CTE vs Subquery
| Feature | CTE | Subquery |
|---|---|---|
| Syntax | Defined with WITH keyword before main query | Nested inside the main query in parentheses |
| Readability | ✅ Much easier to read — named blocks | ⚠️ Gets messy when deeply nested |
| Reusability | ✅ Can be referenced multiple times in one query | ❌ Must be repeated every time |
| Recursion | ✅ Supports recursive queries | ❌ Cannot be recursive |
| WHERE clause use | ⚠️ Cannot be used directly in WHERE | ✅ Works great in WHERE with IN / EXISTS |
| UPDATE statements | ⚠️ Limited support | ✅ Can be used to pull values for UPDATE |
| Debugging | ✅ Easy — test each CTE block independently | ⚠️ Harder — must untangle nested levels |
| Database support | MySQL 8+, PostgreSQL, SQL Server, SQLite 3.35+ | All SQL databases |
| Performance | Similar — depends on database optimizer | Similar — may be slower if referenced multiple times |
5. When to Use a CTE
Reach for a CTE in these situations:
✅ Use CTE when...
- Your query has multiple logical steps
- You need to reuse the same result set more than once
- You want code that's easy to read and maintain
- You're working with hierarchical / recursive data
- You're writing a complex report or analysis query
- You want to debug one step at a time
⚠️ Don't use CTE when...
- You need it in a WHERE clause with IN or EXISTS
- You're writing a quick, simple one-off lookup
- You're using an older database (MySQL < 8.0)
- You need to use the result to UPDATE another table
SELECT
MONTH(order_date) AS month,
SUM(amount) AS revenue
FROM orders
GROUP BY MONTH(order_date)
)
-- Used once: total revenue
SELECT SUM(revenue) AS annual_total FROM monthly_revenue
UNION ALL
-- Used again: average monthly revenue
SELECT AVG(revenue) AS avg_monthly FROM monthly_revenue;
-- monthly_revenue is defined once but used twice — no repetition!
6. When to Use a Subquery
Subqueries have situations where they genuinely outshine CTEs — don't dismiss them:
✅ Use Subquery when...
- You need a simple, one-time inline filter
- You're using IN, EXISTS, or NOT EXISTS in WHERE
- You need to pull a single value to use in UPDATE
- Your database is older (MySQL < 8.0)
- The query is short enough that nesting doesn't hurt readability
⚠️ Don't use Subquery when...
- You need the same result in multiple places
- Your query has 3+ levels of nesting
- You're working with hierarchical / recursive data
- Readability and maintenance are priorities
SELECT customer_name, email
FROM customers
WHERE customer_id IN (
SELECT DISTINCT customer_id
FROM orders
WHERE order_date >= '2026-02-01'
AND order_date < '2026-03-01'
);
UPDATE products
SET price = (
SELECT AVG(price)
FROM products
WHERE category = 'Electronics'
)
WHERE product_id = 42;
7. Performance: Which Is Faster?
Performance between CTEs and subqueries is often identical — modern SQL optimizers treat them the same way in most cases.
This is the question everyone asks — and the honest answer is: it depends, and usually it doesn't matter much.
Here's what the research and real-world experience shows:
- 🔁 Modern database optimizers (PostgreSQL, SQL Server, MySQL 8+) often generate the same execution plan for equivalent CTEs and subqueries — so performance is identical.
- ⚡ Subqueries used multiple times can be slower — because some databases re-execute the subquery every time it's referenced. A CTE may be computed once and stored (known as CTE materialization).
- 📊 CTEs in PostgreSQL are sometimes materialized as a separate step, which can occasionally be slower than an equivalent subquery — or faster, depending on the data.
- 🔍 Correlated subqueries (subqueries that reference the outer query row by row) can be significantly slower on large datasets — avoid them on tables with millions of rows.
EXPLAIN ANALYZE on your query to see the actual execution plan — then optimize from there.
8. Bonus: Recursive CTEs (What Subqueries Can't Do)
This is where CTEs have a clear, undisputed advantage — recursive queries. A recursive CTE can reference itself, making it the only SQL tool for traversing hierarchical data like org charts, product categories, or file systems.
Here's a practical example: finding all employees under a specific manager in an org hierarchy:
-- Anchor: start with the top manager
SELECT employee_id, employee_name, manager_id, 1 AS level
FROM employees
WHERE employee_id = 1
-- (Start from employee ID 1 = the CEO)
UNION ALL
-- Recursive part: find all employees under each manager
SELECT e.employee_id, e.employee_name, e.manager_id, oh.level + 1
FROM employees e
JOIN org_hierarchy oh ON e.manager_id = oh.employee_id
)
SELECT employee_name, level
FROM org_hierarchy
ORDER BY level, employee_name;
9. Quick Decision Guide
Not sure which to use? Run through this checklist:
🧭 CTE vs Subquery — Decision Guide
10. Best Books and Courses to Master SQL CTEs and Subqueries
Recommended Online Courses:
- 🎓 SQL Intermediate Track on DataCamp — Interactive hands-on track covering CTEs, subqueries, and window functions with real datasets. One of the best ways to practice advanced SQL concepts. (Affiliate link — coming soon)
- 🎓 LeetCode SQL Problems (Free) — Practice CTEs and subqueries with real SQL interview questions used by top tech companies. Visit leetcode.com/problemset/database — completely free.
Key Takeaways
- ✅ A subquery is a query nested inside another query in parentheses — best for simple, one-time inline operations.
- ✅ A CTE is a named temporary result set defined with
WITH— best for complex, multi-step queries that need to be readable and reusable. - ✅ CTEs can be referenced multiple times in a query — subqueries must be repeated every time, leading to redundant code.
- ✅ Only CTEs support recursive queries — essential for hierarchical data like org charts, category trees, and folder structures.
- ✅ Subqueries are still the right tool for WHERE clauses with IN/EXISTS and for pulling single values in UPDATE statements.
- ✅ Performance is usually similar — optimize based on profiling, not assumption. Prioritize readability first.
- ✅ When in doubt: if your query has more than two logical steps, use a CTE. Your future self (and your teammates) will thank you.
Frequently Asked Questions
Conclusion: CTEs and Subqueries Are Tools, Not Rivals
The CTE vs subquery debate isn't really a debate — they're complementary tools that each have their place. The best SQL writers know both well and reach for the right one depending on the situation.
As a general rule of thumb: default to CTEs for anything complex, and use subqueries when the situation specifically calls for them (WHERE/IN/EXISTS filtering, simple single-use lookups, or UPDATE statements). Your code will be more readable, easier to debug, and far easier for colleagues to understand.
The best way to get comfortable with both? Write the same query both ways and compare. That habit alone will take your SQL skills to the next level faster than any tutorial. 🚀
📌 Keep Building Your SQL Skills!
Check out our Complete Beginner's Guide to SQL, How to Delete Duplicate Rows in SQL, and SQL for Financial Analysis — perfect next reads on your SQL journey.
0 Comments