When to Use FROM Subqueries vs CTEs
You'll learn: How to choose between derived tables (FROM subqueries) and Common Table Expressions based on readability, reusability, and context.
The Core Decision
Both FROM subqueries (derived tables) and CTEs solve the same fundamental problem: creating temporary result sets within a query. The choice between them comes down to complexity, reusability, and personal/team preference.
FROM subqueries are embedded inline:
SELECT t.category, AVG(t.price)
FROM (SELECT category, price FROM products WHERE active = 1) t
GROUP BY t.category;
CTEs are named and defined upfront (we'll cover these in detail soon):
WITH active_products AS (
SELECT category, price FROM products WHERE active = 1
)
SELECT category, AVG(price)
FROM active_products
GROUP BY category;
When to Use FROM Subqueries
- Simple, one-time transformations: When you need a quick intermediate step and won't reference it again
- Short queries: When the subquery is just a few lines
- Legacy systems: Some older SQL environments don't support CTEs
When CTEs Are Better (Coming Soon!)
- Complex queries: When you need multiple derived tables or nested subqueries
- Reusability: When you'll reference the same result set multiple times
- Readability: When you want to name intermediate steps clearly
- Recursive operations: CTEs support recursion; subqueries don't
Think of FROM subqueries like inline calculations in math (doing 2(3+4) right in your formula), while CTEs are like defining variables first (let x = 3+4, then 2x).
Key Takeaway: Use FROM subqueries for simple, one-off transformations; prefer CTEs (which we'll learn next) when your query involves multiple steps, needs reusability, or would benefit from clearer naming and structure.