Learning Tips | Published Nov 16, 2026 | 8 min read

SQL Common Table Expressions: CTEs and Recursive Queries

Learn when to use SQL CTEs, how recursive queries expand hierarchies, how to prevent cycles, and why execution plans matter.

Diagram showing a recursive SQL CTE seed rows expanding into manager reports over repeated query steps until no more matches remain

A common table expression (CTE) gives a subquery a name inside one SQL statement. It can make a long query easier to read by separating steps, and a recursive CTE can walk hierarchical data such as an employee reporting tree. This guide shows both patterns and explains why a CTE is not automatically a performance optimization.

What a CTE does

A CTE is introduced with WITH and can be referenced by name in the main statement. It exists for that statement; it does not create a permanent table. Think of it as a named query step that can make the intent of a larger statement clearer.

For example, suppose a report needs each region's total sales and then only wants regions above a threshold:

WITH regional_sales AS (
    SELECT region_id, SUM(amount) AS total_sales
    FROM orders
    WHERE status = 'paid'
    GROUP BY region_id
)
SELECT region_id, total_sales
FROM regional_sales
WHERE total_sales > 100000
ORDER BY total_sales DESC;

The CTE names the aggregation step. A reader can understand how regional_sales is built before looking at the final filter and sort. The same query could often be written as a derived table in FROM; use the form that makes the logic clearest.

Build a query in named steps

Multiple CTEs can refer to earlier CTEs, which is useful when each step has one clear job. In this PostgreSQL example, the first step aggregates paid orders and the second adds a readable label from the region table:

WITH regional_sales AS (
    SELECT region_id, SUM(amount) AS total_sales
    FROM orders
    WHERE status = 'paid'
    GROUP BY region_id
),
ranked_regions AS (
    SELECT region_id,
           total_sales,
           DENSE_RANK() OVER (ORDER BY total_sales DESC) AS sales_rank
    FROM regional_sales
)
SELECT r.name, rr.total_sales, rr.sales_rank
FROM ranked_regions AS rr
JOIN regions AS r ON r.id = rr.region_id
WHERE rr.sales_rank <= 5
ORDER BY rr.sales_rank, r.name;

Each step has a name and a defined output. Avoid splitting a short, obvious query into many tiny CTEs; extra layers can make a simple task harder to follow. Also give output columns useful names rather than relying on ambiguous SELECT * when later steps join multiple sources.

Use a recursive CTE for a hierarchy

A recursive CTE refers to its own name. It has a starting query, often called the anchor, and a recursive term that reads rows produced by the previous iteration. The database repeats the recursive term until it produces no more rows. The example below uses PostgreSQL array syntax to carry an ancestry path and avoid following a node twice on the same path:

WITH RECURSIVE team_tree(id, manager_id, name, depth, path) AS (
    SELECT id, manager_id, name, 0, ARRAY[id]
    FROM employees
    WHERE id = 1  -- replace with the selected manager

    UNION ALL

    SELECT e.id, e.manager_id, e.name,
           t.depth + 1,
           t.path || e.id
    FROM employees AS e
    JOIN team_tree AS t ON e.manager_id = t.id
    WHERE NOT e.id = ANY(t.path)
)
SELECT id, manager_id, name, depth
FROM team_tree
ORDER BY path;

The anchor returns the chosen starting employee. Each recursive pass finds direct reports for the current rows and increments their depth. The final query returns the accumulated hierarchy. The path check prevents cycles along a branch, which matters if malformed data lets a manager chain point back to an earlier employee. This path syntax is PostgreSQL-specific; check your database's documentation before reusing it elsewhere.

Make recursion terminate safely

Every recursive query needs a clear stopping condition. In the example, recursion stops naturally when the current employees have no matching reports; the path guard also prevents a cycle from repeating forever. Add a domain-appropriate depth bound when a maximum hierarchy depth is known, and consider what the query should do if the data contains invalid relationships.

UNION and UNION ALL have different duplicate behavior. UNION removes duplicate complete rows between iterations, while UNION ALL keeps them and often avoids that deduplication work. Neither choice replaces understanding the graph: if a recursive row includes a changing depth or path, revisiting the same entity may still produce a different row. Use explicit cycle detection when cycles are possible.

CTEs do not guarantee faster execution

A CTE is primarily a way to express a query. Whether it is materialized, inlined, or optimized together with the surrounding statement depends on the database engine, query shape, and sometimes engine version. In PostgreSQL, a side-effect-free, non-recursive CTE used once can generally be folded into its parent query; other cases may be handled differently. Do not assume that adding WITH creates an optimization fence or makes the query faster.

When a query is slow, inspect its execution plan and test with representative data. In PostgreSQL, EXPLAIN (ANALYZE, BUFFERS) executes the statement to measure it; use caution with statements that modify data. Compare the actual plan and timings before deciding whether a CTE, derived table, or another design is better.

Practical checklist

  • Use a CTE when naming a meaningful step makes a larger statement easier to understand.
  • Keep each step focused and name its output columns clearly.
  • Use a recursive CTE only when the data is naturally hierarchical or graph-shaped.
  • Design a termination condition and cycle strategy instead of relying on accidental duplicate behavior.
  • Check your database's syntax and planner rules; do not assume every engine treats CTEs the same way.
  • Use execution plans and representative measurements to investigate performance.

A well-designed CTE improves the explanation of a query by making its stages visible. For recursive data, the anchor, repeated step, and stopping rule should be understandable on their own before the query is used in production.

Further reading

Want a structured Java learning path?

Explore Jaipur Engineers course options to connect Java fundamentals, debugging, backend development and project practice in a guided learning plan.

Explore the Java program   Ask for course guidance