SQL WITH RECURSIVE – Master Tree & Hierarchy Queries

Introduction – Why Learn Recursive CTEs?

Ever needed to query a hierarchical dataset—like organizational charts, category trees, or file systems? Traditional SQL can struggle here. Enter WITH RECURSIVE, a powerful extension of Common Table Expressions (CTEs) that allows SQL queries to call themselves recursively.

This feature lets you navigate hierarchical or tree-structured data elegantly—without stored procedures or client-side loops.

In this guide, you’ll learn:

• What WITH RECURSIVE does and when to use it
• Syntax and structure
• Real-world examples
• Performance considerations and tips

1. What Is WITH RECURSIVE in SQL?

WITH RECURSIVE is a feature that allows a Common Table Expression (CTE) to refer to itself. It’s most commonly used for:

• Tree traversal (e.g., parent-child)
• Graph algorithms (e.g., shortest path)
• Building paths or hierarchies

Supported in:

• PostgreSQL
• MySQL 8+
• SQL Server (using CTE without RECURSIVE keyword but same structure)

2. Syntax of Recursive CTE

WITH RECURSIVE cte_name (column_list) AS (
  -- Anchor member (base query)
  SELECT ...

  UNION ALL

  -- Recursive member (calls itself)
  SELECT ... FROM cte_name
  JOIN ...
)
SELECT * FROM cte_name;

Explanation:

• Anchor member: Base case (e.g., top-level employee)
• Recursive member: Refers to CTE itself to walk down the hierarchy
• UNION ALL combines the results

3. Real-World Example: Employee Hierarchy

Sample employees Table

Query: Get Full Org Chart Starting from CEO

WITH RECURSIVE employee_hierarchy AS (
  SELECT id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT e.id, e.name, e.manager_id, eh.level + 1
  FROM employees e
  JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy;

Explanation:

• Starts at the CEO (manager_id IS NULL)
• Joins each employee with their manager recursively
• Adds a level column to indicate hierarchy depth

4. Recursive CTE with Path Generation

WITH RECURSIVE category_paths(id, name, path) AS (
  SELECT id, name, name AS path
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL

  SELECT c.id, c.name, CONCAT(cp.path, ' > ', c.name)
  FROM categories c
  JOIN category_paths cp ON c.parent_id = cp.id
)
SELECT * FROM category_paths;

Tip: Use string concatenation to build breadcrumb paths like Electronics > Computers > Laptops.

5. Recursive Depth Limitation

PostgreSQL

WITH RECURSIVE limited_cte AS (
  SELECT ... , 1 as level
  FROM ...

  UNION ALL

  SELECT ..., level + 1
  FROM limited_cte
  WHERE level < 5
)

Avoid infinite recursion with a level or depth limit clause.

6. Use Cases of WITH RECURSIVE

Tips & Gotchas

Summary – Recap & Next Steps

WITH RECURSIVE enables powerful hierarchical and graph-based queries in SQL, allowing you to traverse parent-child data without writing procedural code.

Key Takeaways:

• Use a CTE with an anchor and recursive part.
• Combine results using UNION ALL.
• Always use depth limits to control recursion.
• Ideal for org charts, BOMs, and tree structures.

Real-World Relevance:
Used in ERPs, file systems, organizational tools, and any application involving tree-like data structures.

FAQ – SQL WITH RECURSIVE

Is WITH RECURSIVE supported in all databases?

Mostly:

• PostgreSQL, MySQL 8+, SQLite: Yes (with WITH RECURSIVE)
• SQL Server: Yes, structure is similar but no RECURSIVE keyword needed

What’s the difference between CTE and Recursive CTE?

Regular CTE runs once. Recursive CTE refers to itself and runs iteratively until no rows are returned from the recursive part.

How do I stop infinite recursion?

Use a condition like WHERE level < 10 or WHERE id IS NOT NULL in the recursive clause.

Can I use recursion to replace loops?

Yes. Recursive CTEs replace many procedural loops, especially for tree-like structures.

What does UNION ALL do in recursion?

It merges the anchor and recursive results together. ALL improves performance by skipping duplicate checks.