Introduction
Picture this: you're staring at a SQL query that works, but it's an ugly nested mess... a query inside a query inside another query. There's a cleaner way to write it, but every time you try to refactor it, you break something.
If that sounds familiar, you're about to meet two of SQL's most powerful tools: subqueries and Common Table Expressions (CTEs). Both let you break complex problems into smaller pieces. Both can answer the same question. But they are not the same thing and knowing when to reach for which one is what separates "SQL that works" from "SQL that's actually good."
What Is a Subquery?
A subquery (also called an inner query or nested query) is simply a query nested inside another query. It runs first, and its result is used by the outer query to complete its job.
Subqueries can live in several places:
- Inside a
WHEREclause (to filter results) - Inside a
SELECTclause (to compute a value per row) - Inside a
FROMclause (to act as a temporary table)
Example: A subquery in WHERE:
SELECT product_name, price
FROM products
WHERE price > (
SELECT AVG(price)
FROM products
);
Here, the inner query calculates the average price across all products, and the outer query uses that single value to filter for products priced above average.
Example: A subquery in FROM:
SELECT dept_id, avg_salary
FROM (
SELECT dept_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY dept_id
) AS dept_averages
WHERE avg_salary > 50000;
This is often called a derived table :- The subquery produces a temporary result set that the outer query treats like a regular table.
What Is a CTE?
A Common Table Expression (CTE) is a named, temporary result set defined using the WITH keyword, which you can then reference like a table within your main query. Think of it as giving a subquery a name tag and a seat at the table — literally.
Basic syntax:
WITH dept_averages AS (
SELECT dept_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY dept_id
)
SELECT dept_id, avg_salary
FROM dept_averages
WHERE avg_salary > 50000;
Notice anything? This does the exact same thing as the derived-table subquery example above, but it reads top-to-bottom instead of inside-out. That readability difference becomes huge as queries grow more complex.
CTEs have a superpower subqueries don't: recursion. A recursive CTE can reference itself, which is invaluable for hierarchical data like org charts, category trees, or bill-of-materials structures.
WITH RECURSIVE org_chart AS (
SELECT employee_id, name, manager_id, 1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.employee_id, e.name, e.manager_id, oc.level + 1
FROM employees e
JOIN org_chart oc ON e.manager_id = oc.employee_id
)
SELECT * FROM org_chart;
No subquery can pull this off. This alone makes CTEs indispensable for hierarchical or graph-like data.
Subqueries vs CTEs: The Key Differences
| Aspect | Subquery | CTE |
|---|---|---|
| Syntax style | Nested inside another query | Declared upfront with WITH, referenced by name |
| Readability | Can get messy with multiple nesting levels | Reads linearly, easier to follow |
| Reusability | Must repeat the same subquery if used more than once | Can be referenced multiple times in the same query |
| Recursion | Not supported | Supported via WITH RECURSIVE
|
| Multiple definitions | Awkward to chain several subqueries | Can chain multiple CTEs in one WITH clause |
| Performance | Generally similar; depends on the query optimizer | Generally similar; some databases materialize CTEs, which can help or hurt depending on context |
| Debugging | Harder to test in isolation | Easier — you can run just the CTE block to sanity-check it |
The performance point is worth a caveat: in some database engines (older PostgreSQL versions, for instance), CTEs were "optimization fences," meaning the engine wouldn't push filters into them the way it would a subquery. Modern PostgreSQL (12+) has largely fixed this by inlining non-recursive CTEs automatically. Always check your specific database's behavior before assuming one is faster than the other.
Practical Examples and Use Cases
1. Filtering with an aggregate — Subquery shines for simple, one-off checks
SELECT customer_name
FROM customers
WHERE customer_id IN (
SELECT customer_id
FROM orders
WHERE order_total > 1000
);
Quick, contained, doesn't need a name — a subquery is perfect here.
2. Multi-step analysis — CTEs shine when you chain logic
WITH monthly_sales AS (
SELECT DATE_TRUNC('month', order_date) AS month, SUM(order_total) AS total_sales
FROM orders
GROUP BY 1
),
sales_growth AS (
SELECT month, total_sales,
LAG(total_sales) OVER (ORDER BY month) AS prev_month_sales
FROM monthly_sales
)
SELECT month, total_sales, prev_month_sales,
ROUND(((total_sales - prev_month_sales) / prev_month_sales) * 100, 2) AS growth_pct
FROM sales_growth;
Here, sales_growth builds directly on monthly_sales — try nesting that logic three levels deep as subqueries and you'll see why CTEs win for multi-step transformations.
3. Hierarchical data :- Only CTEs can do this
Think reporting structures, folder trees, or category hierarchies (like a product catalog with parent/child categories). Recursive CTEs are the only clean way to traverse these without writing procedural code.
4. Reusing the same logic multiple times in one query
If you need the same intermediate result in both your SELECT and a JOIN, a CTE saves you from writing (and maintaining) the same subquery twice and if the logic ever needs to change, you only update it in one place.
Conclusion
Subqueries and CTEs aren't rivals... they're two tools in the same toolbox, each earning their keep in different situations. Reach for a subquery when you need a quick, self-contained calculation or filter that's used once. Reach for a CTE when your query has multiple logical steps, when you need to reuse the same result set more than once, or when you're dealing with recursive, hierarchical data.
The real skill isn't picking a "winner", it's recognizing which one makes your query easier to read, debug, and maintain. Master both!
Top comments (1)
Let's interact!!😙