Understanding CTEs and Recursive CTEs in PostgreSQL (With Step-by-Step Execution)

When working with SQL queries, things can quickly become difficult to read and maintain — especially when dealing with complex subqueries or hierarchical data.

Two powerful tools in PostgreSQL that solve these problems are:

• Common Table Expressions (CTEs)
• Recursive CTEs

In this article, we’ll explore:

1. What CTEs are
2. Why they are useful
3. How recursive CTEs work
4. Step-by-step execution of recursive queries
5. Practical examples

What is a CTE (Common Table Expression)?

A Common Table Expression (CTE) is a temporary result set that exists only for the duration of a query.

It allows you to define a query once and reference it like a table inside another query.

Think of a CTE as:

A temporary table created within a query to simplify complex SQL logic.

CTEs are defined using the WITH clause.

Basic Syntax

WITH table_name AS (
    SELECT ...
)
SELECT * FROM table_name;

The query inside the WITH clause executes first, and its result behaves like a temporary table.

Why Use CTEs?

Many queries can also be written using subqueries, but CTEs provide several advantages.

1️Improved Readability

Complex queries become much easier to understand.

2️ Logical Separation

You can break a large query into smaller logical parts.

3️ Reusability

The same CTE can be referenced multiple times within the query.

4️ Debugging Simplicity

You can test the CTE separately before integrating it into the final query.

Example: CTE vs Subquery

Consider an employees table:

Using a Subquery

SELECT *
FROM (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
) dept_avg
WHERE avg_salary > 6000;

Using a CTE

WITH department_average AS (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
)
SELECT *
FROM department_average
WHERE avg_salary > 6000;

The CTE version is cleaner and easier to read.

Multiple CTEs

You can also define multiple CTEs in a single query.

WITH department_average AS (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
),
high_salary_departments AS (
    SELECT *
    FROM department_average
    WHERE avg_salary > 6000
)
SELECT * FROM high_salary_departments;

This approach is very useful when building multi-stage transformations.

What is a Recursive CTE?

A Recursive CTE is a special type of CTE used to traverse hierarchical or tree-like data.

Typical use cases include:

• Organization hierarchies
• Folder/file systems
• Comment threads
• Category trees
• Graph traversal

For example:

CEO
 ├── Manager A
 │     ├── Employee 1
 │     └── Employee 2
 └── Manager B
       └── Employee 3

If we want to find all employees under a manager, recursive CTEs make it possible.

Recursive CTE Syntax

Recursive CTEs consist of two parts:

1️ Anchor Query (Base Query)
2️ Recursive Query

They are combined using UNION ALL.

WITH RECURSIVE hierarchy_table AS (
    -- Anchor Query
    SELECT ...
   UNION ALL
    -- Recursive Query
    SELECT ...
    FROM hierarchy_table
    JOIN other_table ...
)
SELECT * FROM hierarchy_table;

Example Table: Employee Hierarchy

Consider this table:

Here:

• manager_id references the employee’s manager

Recursive CTE Example

Goal:

Given a manager, find all employees under that manager.

WITH RECURSIVE hierarchy_table AS (
    -- Anchor Query
    SELECT 
        id,
        name,
        dept_name,
        manager_id,
        0 AS level
    FROM employee_table
    WHERE id = 2
    UNION ALL
    -- Recursive Query
    SELECT 
        e.id,
        e.name,
        e.dept_name,
        e.manager_id,
        h.level + 1 AS level
    FROM hierarchy_table h
    LEFT JOIN employee_table e
    ON h.id = e.manager_id
)
SELECT * FROM hierarchy_table;

What Does This Query Do?

This query finds:

All employees that fall under a given manager in the hierarchy.

If the manager is Manager A (id = 2), the result will be:

How Recursive CTEs Run (Step-by-Step)

Understanding the execution process is key to mastering recursive queries.

Recursive CTE execution follows an iterative process.

Step 1 — Run the Anchor Query

The anchor query runs first.

SELECT * FROM employee_table WHERE id = 2

Result:

This result becomes the initial recursive table.

Step 2 — Run the Recursive Query

The recursive query runs using the result from Step 1.

SELECT e.*
FROM hierarchy_table h
JOIN employee_table e
ON h.id = e.manager_id

Now it finds employees whose manager is 2.

Result:

Step 3 — Repeat the Recursive Query

Now the new rows become the next input.

The recursive query runs again using:

4,5

If these employees have subordinates, they will appear.

If not, recursion stops.

Step 4 — Termination Condition

Recursion stops when:

1️ The recursive query returns no new rows

or

2️ A termination condition is reached (e.g., depth limit).

Example termination condition:

WHERE h.level < 5

Step 5 — Final Query Execution

After recursion finishes, the outer query runs:

SELECT * FROM hierarchy_table;

This returns the complete hierarchy discovered during recursion.

Visual Execution Flow

Recursive CTE execution can be visualized as:

Anchor Query
     │
     ▼
Initial Rows
     │
     ▼
Recursive Query
     │
     ▼
Next Level Rows
     │
     ▼
Recursive Query
     │
     ▼
Next Level Rows
     │
     ▼
Stop Condition Met
     │
     ▼
Final Result

Real-World Use Cases

Recursive CTEs are widely used in:

Organizational Hierarchies

Finding employees under a manager.

File Systems

Traversing folders and subfolders.

Category Trees

Products and nested categories.

Graph Traversal

Finding connected nodes.

Comment Threads

Nested comments in forums or blogs.

Key Takeaways

• CTEs allow you to define temporary query results inside a SQL statement.
• They improve readability and maintainability of complex queries.
• Recursive CTEs allow traversal of hierarchical structures.
• Recursive queries consist of => Anchor query & Recursive query
• Execution continues until no more rows are produced or a termination condition is met.

Final Thoughts

Recursive CTEs are one of the most powerful SQL features in PostgreSQL.

They allow developers to work with hierarchical data without writing complex loops or multiple joins.

Once you understand how the anchor query and recursive query interact, recursive CTEs become an incredibly elegant way to model tree traversal directly inside SQL.

Understanding CTEs and Recursive CTEs in PostgreSQL (With Step-by-Step Execution) was originally published in Level Up Coding on Medium, where people are continuing the conversation by highlighting and responding to this story.