Write Clearer SQL: When to Use CTEs or Subqueries

CTEs and subqueries can often produce the same result. The important difference is not what they return, but how clearly they communicate the steps of your query.

That distinction matters when a query grows beyond one filter or calculation. A compact subquery can keep simple logic close to where it is used. A Common Table Expression (CTE) can give each stage of a longer query a name, making the whole statement easier to read, test, and maintain.

This article uses PostgreSQL-style syntax, but the main ideas apply to most relational databases.

Start with the mental model

A subquery is a query placed inside another SQL statement. It is wrapped in parentheses, and its result is used by the outer query.

A CTE is a named result set defined at the beginning of a statement with WITH. It exists only while that statement runs; it is not a permanent table.

Think of the difference this way:

• A subquery is an expression inside a larger sentence.
• A CTE is a named paragraph that the final query can reference.

Neither is automatically better. The surrounding problem determines which structure communicates the logic more clearly.

The main types of CTEs

CTEs are usually divided into two main types based on how they behave:

• Non-recursive CTE: Runs its query once and returns an intermediate result to the statement that follows. Use it to organize filters, joins, aggregations, or multi-step calculations. Most CTEs you write will be non-recursive.
• Recursive CTE: Includes a recursive member that references the CTE itself. It repeatedly adds related rows until no more rows match, making it suitable for employee hierarchies, category trees, folder structures, and similar data.

You may also hear the terms standalone CTE and nested CTE. These describe structure rather than separate behavioral types:

• A standalone CTE is defined once and used directly by the main SELECT, INSERT, UPDATE, or DELETE statement.
• A nested or chained CTE is commonly used to describe multiple CTEs in one WITH clause where a later CTE reads from an earlier one. This creates a sequence of named steps. It is not the same as placing one WITH clause inside another, and some databases restrict where nested WITH clauses may appear.

The first CTE example later in this article is standalone and non-recursive. The multi-stage salary example is chained and non-recursive, while the organization example is recursive.

Example 1: A standalone, non-recursive CTE

When a calculation feeds several parts of a query, a CTE makes the stages visible:

-- Step 1: create one average-salary row per department.
WITH department_averages AS (
    SELECT department, AVG(salary) AS average_salary
    FROM employees
    GROUP BY department
)
-- Step 2: compare each employee with the named intermediate result.
SELECT
    e.employee_name,
    e.department,
    e.salary,
    d.average_salary
FROM employees AS e
JOIN department_averages AS d
    ON d.department = e.department
WHERE e.salary > d.average_salary;

Read it from top to bottom:

1. Calculate the average salary for every department.
2. Join those averages to the employees.
3. Keep employees whose salary is above their department's average.

The CTE does not merely shorten the query. Its name, department_averages, explains what the intermediate result means. That becomes more valuable when you add more aggregations, joins, or business rules.

Example 2: Chained non-recursive CTEs

You can also define multiple CTEs in one WITH clause. Each CTE should represent one understandable step, and later CTEs can build on earlier ones:

-- Step 1: summarize salary data by department.
WITH department_totals AS (
    SELECT department, SUM(salary) AS total_salary
    FROM employees
    GROUP BY department
),
-- Step 2: calculate a benchmark from the summarized rows.
average_department_total AS (
    SELECT AVG(total_salary) AS average_total
    FROM department_totals
)
-- Step 3: return departments above that benchmark.
SELECT d.department, d.total_salary
FROM department_totals AS d
CROSS JOIN average_department_total AS a
WHERE d.total_salary > a.average_total;

The second CTE consumes the result of the first, and the final query gives the complete calculation a clear direction. Still, avoid splitting a query into tiny pieces just to use more CTEs; names help only when they clarify meaningful stages.

Example 3: A recursive CTE

A recursive CTE can reference itself. This makes it useful for hierarchical data such as employee-manager relationships, categories, and folder trees.

Assume manager_id points to another row in employees:

WITH RECURSIVE organization AS (
    -- Anchor member: begin with employees who have no manager.
    SELECT employee_id, employee_name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive member: find the next level in the hierarchy.
    SELECT e.employee_id, e.employee_name, e.manager_id, o.level + 1
    FROM employees AS e
    JOIN organization AS o
        ON e.manager_id = o.employee_id
)
-- Read the completed hierarchy after recursion stops.
SELECT employee_name, manager_id, level
FROM organization
ORDER BY level, employee_id;

The first query is the anchor member: it selects the top-level manager. The second is the recursive member: it finds employees managed by rows already discovered. Recursion continues until no new rows match.

PostgreSQL uses WITH RECURSIVE. SQL Server uses WITH and supports the MAXRECURSION query hint to limit recursive iterations. Because recursion controls differ by database, check your database documentation and ensure the recursive member always moves toward a stopping condition.

The CTE examples show how named query steps work in practice. Once those patterns are clear, we can compare them with subqueries and see when a smaller, local expression is the better choice.

Now compare subqueries

Suppose an employees table contains employee_id, employee_name, department, and salary. To find employees who earn more than the company-wide average, a scalar subquery is a natural fit:

-- The outer query returns the employees we want to inspect.
SELECT employee_name, salary
FROM employees
WHERE salary > (
    -- This scalar subquery returns one value: the company average.
    SELECT AVG(salary)
    FROM employees
);

The inner query returns one value: the average salary. The outer query compares each employee's salary with that value. Because the calculation is short, used once, and directly related to the filter, giving it a separate name would add little value.

Subqueries can appear in WHERE, SELECT, FROM, and HAVING. They may return one value, one row, multiple rows, or a table-like result. The operator must match that shape: use = for one value, for example, and IN or EXISTS when working with a set of rows.

EXISTS is especially useful when you only need to know whether a related row is present. It stops being about values from the inner query; the question becomes true or false:

-- Return departments that have at least one highly paid employee.
SELECT d.department_name
FROM departments AS d
WHERE EXISTS (
    -- SELECT 1 signals that the returned columns do not matter.
    SELECT 1
    FROM employees AS e
    WHERE e.department_id = d.department_id
      AND e.salary > 100000
);

The inner query is correlated through department_id. For each department, the database checks whether a matching employee exists. This is often clearer than joining tables and then removing duplicate departments.

A correlated subquery is different because it references the current row of the outer query:

-- Evaluate every employee in the outer query.
SELECT e.employee_name, e.department, e.salary
FROM employees AS e
WHERE e.salary > (
    -- Recalculate the average for the outer employee's department.
    SELECT AVG(d.salary)
    FROM employees AS d
    WHERE d.department = e.department
);

This asks whether each employee earns more than the average for their own department. It is expressive, but correlated subqueries can require repeated work. On large tables, compare the execution plan with an equivalent join or CTE instead of assuming which form is faster.

CTE or subquery? Use this checklist

Situation	Prefer
A short calculation used once	Subquery
A simple membership or existence check	Subquery with IN or EXISTS
Several nested levels are becoming hard to follow	CTE
The query has clear, sequential transformation steps	CTE
The same named result is referenced more than once	CTE, then inspect the plan
Hierarchical or graph-like traversal is required	Recursive CTE

Common mistakes to avoid

Using = with a multi-row result. A scalar comparison expects one value. If the subquery can return several rows, use an appropriate set operator such as IN, ANY, ALL, or EXISTS.

Ignoring NULL with NOT IN. If the inner result contains NULL, NOT IN can evaluate to unknown and return no rows. A correlated NOT EXISTS is often safer when expressing "no matching row."

Assuming a CTE stores data. A CTE exists only for one statement. Use a temporary table when results must survive across statements or need their own indexes.

Writing recursion without a stopping path. The recursive member must eventually stop finding rows. Cycles in hierarchical data can otherwise cause repeated work or hit a database recursion limit.

Readability is the first decision, not the last. If the subquery is understandable at a glance, keep it. If readers must count parentheses or mentally execute several nested levels, name the stages with CTEs.

Performance is database- and query-specific. A CTE is not automatically materialized, cached, or faster than a subquery. Optimizers may inline, materialize, or transform expressions differently. Use EXPLAIN or your database's execution-plan tool with realistic data before making performance claims.

The practical rule is simple: keep local logic local with a subquery, and use a CTE when naming the steps makes the query easier to reason about. Clear SQL is easier to review today and safer to change later.