SQL- Subqueries & CTEs

If you've spent any time writing SQL beyond basic SELECT statements, you've run into situations where a single query just isn't enough on its own, you need to query the results of another query.
That's where subqueries and Common Table Expressions (CTEs) come in.

Both let you break a complex problem into smaller pieces, but they behave differently, read differently, and shine in different situations. This article walks through what each one is, how they compare, and where you'd actually reach for one over the other.

What Is a Subquery?

A subquery (or inner query) is a query nested inside another SQL statement. It runs first, and its result is used by the outer query.

Subqueries can appear in several places:

• In the WHERE clause (to filter rows)
• In the FROM clause (as a derived table)
• In the SELECT clause (to return a single value per row)
• Inside INSERT, UPDATE, or DELETE statements

Example: Subquery in a WHERE clause

Say you have an orders table and want to find customers who placed an order above the average order value.

SELECT customer_id, order_id, amount
FROM orders
WHERE amount > (
    SELECT AVG(amount)
    FROM orders
);

Here, the inner query calculates the average order amount, and the outer query filters orders against that single value.

Example: Subquery in a FROM clause (derived table)

SELECT dept, avg_salary
FROM (
    SELECT department AS dept, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
) AS dept_averages
WHERE avg_salary > 60000;

The inner query produces a temporary result set (dept_averages), which the outer query then treats like any other table.

Example: Correlated subquery

Unlike the examples above, a correlated subquery references a column from the outer query, so it runs once per row of the outer query rather than just once overall.

SELECT e.employee_name, e.salary, e.department
FROM employees e
WHERE e.salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e2.department = e.department
);

This finds employees earning more than the average salary in their own department — the inner query depends on e.department from the outer query, so it can't be evaluated independently.

What Is a CTE?

A Common Table Expression (CTE) is a named, temporary result set defined using a WITH clause, which you can then reference like a table within the main query.

WITH dept_averages AS (
    SELECT department AS dept, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
)
SELECT dept, avg_salary
FROM dept_averages
WHERE avg_salary > 60000;

This produces the exact same result as the derived-table subquery example above — but notice how much easier it is to read top to bottom. You define dept_averages once, give it a clear name, and then use it.

Multiple CTEs

You can chain several CTEs together, and later ones can reference earlier ones:

WITH dept_averages AS (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
),
high_paying_depts AS (
    SELECT department
    FROM dept_averages
    WHERE avg_salary > 60000
)
SELECT e.employee_name, e.department, e.salary
FROM employees e
JOIN high_paying_depts h ON e.department = h.department;

This kind of step-by-step logic is where CTEs really start to pay off — each block does one clear job, and the final query just pulls it together.

Recursive CTEs

CTEs also support recursion, which subqueries cannot do. This is invaluable for hierarchical or graph-like data i.e org charts, category trees, bill-of-materials structures, and so on.

WITH RECURSIVE org_chart AS (
    -- Anchor: top-level managers (no manager)
    SELECT employee_id, employee_name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive step: employees reporting to someone already in org_chart
    SELECT e.employee_id, e.employee_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
ORDER BY level, employee_name;

This walks down the reporting hierarchy level by level — something you simply can't express with a standard subquery.

Key Differences

	Subquery	CTE
Syntax	Nested inside another query (WHERE, FROM, SELECT, etc.)	Declared upfront with WITH ... AS (...)
Readability	Can get hard to follow when nested deeply	Reads top-to-bottom; named and self-documenting
Reusability	Must be rewritten/repeated if needed more than once in the same query	Can be referenced multiple times within the same query
Recursion	Not supported	Supported via WITH RECURSIVE
Scope	Local to the clause it's written in	Available to the entire query that follows it
Multiple steps	Awkward — requires nesting subqueries within subqueries	Natural — chain CTEs one after another

When to Use Which

Reach for a subquery when:

• You need a quick, one-off filter or calculation (e.g., "greater than the average")
• The logic is simple enough that naming it separately would be overkill
• You're writing something inside an IN, EXISTS, or scalar comparison

-- Simple, self-contained — a subquery is perfectly fine here
SELECT product_name
FROM products
WHERE product_id IN (
    SELECT product_id FROM order_items WHERE quantity > 100
);

Reach for a CTE when:

• You have multiple logical steps that build on each other
• You need to reference the same intermediate result more than once
• You're working with hierarchical/recursive data
• Readability and maintainability matter — especially in a query someone else (or future you) will need to debug
• You're prototyping a complex query and want to test each step in isolation (you can run just the CTE block to sanity-check it)

-- Multi-step logic — a CTE keeps this readable and debuggable
WITH monthly_sales AS (
    SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS total
    FROM orders
    GROUP BY 1
),
sales_growth AS (
    SELECT month, total,
           LAG(total) OVER (ORDER BY month) AS prev_month_total
    FROM monthly_sales
)
SELECT month, total, prev_month_total,
       ROUND(((total - prev_month_total) / prev_month_total) * 100, 2) AS pct_growth
FROM sales_growth
WHERE prev_month_total IS NOT NULL;

Practical Use Cases

• Data cleaning/validation: Use a CTE to isolate flagged or invalid rows before joining them back for review — a common pattern in data quality work where you want each validation rule to be its own named step.
• Deduplication: Combine a CTE with ROW_NUMBER() to identify and remove duplicate records.

WITH ranked_rows AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC) AS rn
    FROM customers
)
SELECT * FROM ranked_rows WHERE rn = 1;

• Existence checks: Subqueries with EXISTS are a clean, efficient way to check for related records without pulling their data.

SELECT c.customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

• Hierarchical reporting: Recursive CTEs for org charts, category trees, or any parent-child structure.
• Building a reporting pipeline step by step: Chained CTEs that mirror the way you'd naturally reason through a problem — aggregate, then filter, then rank, then present.

Wrapping Up

Subqueries and CTEs often solve the same underlying problem; needing the result of one query inside another but they differ in readability, reusability, and capability. Subqueries are great for quick, self-contained logic. CTEs shine when a query has multiple steps, needs to be readable months later, or requires recursion.

In practice, many data professionals default to CTEs for anything beyond a trivial filter, simply because breaking a query into named, logical steps makes it far easier to write, debug, and hand off. But understanding both and knowing when a simple subquery is genuinely the cleaner choice, makes you a more versatile SQL writer.