Subqueries vs CTEs: When, Why, How.

With a background in Python programming, Learning SQL started feeling like a step back, every line is independent of the next and so far nothing seemed interconnected or Programming-like, until I hit a wall:

Picture this :

You have a table employees and you need to find employees who earn more than the average salary of their own department, not the company average.

This Problem requires two queries, one to get the average salary of each department and another to find the employee whose salary is above that average.

Such a problem requires thinking like a programmer and the solution is either a subquery or a CTE.
But what are they.

Subqueries

A subquery (or inner query or nested query) is a SELECT statement embedded inside another SQL statement (SELECT, INSERT, UPDATE, DELETE). It answers a question within a question.

SELECT name, salary, department_id
FROM employees e1
WHERE salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e2.department_id = e1.department_id
);

The inner query runs first, finds the average salary then it is used in the outer query.

When should you use subqueries?

1. For Simple Filtering - comparing a column against a single value or a short list in another table.
2. In select or where clauses
3. when the logic is trivial(few lines nested is clean, more lines becomes messy and inefficient)

CTEs

What is it?
A Common Table Expression (CTE) is a temporary, named result set that exists only during the execution of a single query. Think of it as a view that vanishes after the query finishes.

You define a CTE using the WITH clause at the top of your query, then reference it like a regular table.

WITH dept_avg AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT e.name, e.salary, e.department_id
FROM employees e
JOIN dept_avg d ON e.department_id = d.department_id
WHERE e.salary > d.avg_salary;

When to use

1. Breaking down multi-step transformations – Window functions, aggregations, then joins.

2. Reusing the same subquery multiple times – Reference the CTE name twice instead of duplicating code.

3. Recursive queries – Impossible with standard subqueries.

4. Improving readability – Name your steps (sales_summary, returns_calc) to self-document.

Let's now compare the two across four dimensions to further understand, the importance of each technique, the strengths and the weaknesses.

Clear Comparision - Subqueries vs CTEs

1. Readability

Aspect	Subquery	CTE
Short logic	Clear and concise	Overkill
Nested depth > 2	Messy and inefficient	Top-down, Clean
Multiple references	Copy-paste to reuse	Reference by name
Self-documenting	requires comments	Intent-revealing names

CTEs for any query beyond 10 lines. Subqueries become unreadable when nested three levels deep. CTEs let you read the query like a story: Step A -> Step B -> Final SELECT.

1. Perfomance

In theory, CTEs and subqueries often produce the same execution plan because modern optimizers (PostgreSQL, SQL Server, Oracle) treat them similarly.

1. Reusability

2. Scope & Limitations

Feature	Subquery	CTE
Can appear in WHERE, SELECT, HAVING	Yes	must be in WITH before main query
Can be used in UPDATE/DELETE	correlated subqueries	depends on DB support
Can reference outer query correlated	Yes	CTEs are independent
Maximum nesting depth	DB-dependent	Same query can have many CTEs, but no nesting limit

Correlated subqueries are a unique power: the inner query references columns from the outer query, re-evaluating for each row.

Use a Subquery when:

1. You need a scalar value in SELECT or WHERE (e.g., WHERE salary > (SELECT AVG(...))).
2. You need a correlated query that references the outer row.
3. The logic is very simple (one or two lines) and nesting would overcomplicate.
4. Your database version doesn’t support CTEs (rare today).
5. You’re in a WHERE IN clause with a small list from another table.

Use a CTE when:

1. Your query has more than 2 layers of nesting – CTE flattens the pyramid.
2. You need to reference the same derived table multiple times (e.g., join it to itself or use it twice in a UNION).
3. You’re working with hierarchical data (org charts, category trees, recursive paths).
4. You value readability for future maintenance – CTEs act as documentation.
5. You’re building a complex report with steps: WITH cleansed_data AS (...), aggregated AS (...), final AS (...).

Start with a subquery if it’s short and sweet. The moment you find yourself nesting a third SELECT inside a WHERE inside a FROM, stop. Refactor into a CTE. Your future self—and your teammates—will thank you.

Both tools belong in every SQL developer’s belt. Master subqueries for their raw power in filtering and expressions. Master CTEs for their elegance in breaking down chaos into ordered, readable steps. The best SQL isn’t the fastest—it’s the one you can debug at 3 AM.