SQL Recursive CTE

Query hierarchical trees, org charts, and graph data structures in SQL.

❮ Previous Next ❯

A recursive Common Table Expression (CTE) references itself. It is commonly used to traverse hierarchical data like company organizational charts, category trees, and bill of materials.

Key Takeaways: SQL Recursive CTE

Mastering SQL Recursive CTE helps you write clean and reliable SQL code. Follow standard conventions, test edge cases, and keep logic simple. Try running the code example below and complete the exercises to build confidence.

SQL Recursive CTE Code Example

SQL

WITH RECURSIVE OrgChart AS (
  SELECT emp_id, name, manager_id, 1 AS level
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.emp_id, e.name, e.manager_id, o.level + 1
  FROM employees e
  JOIN OrgChart o ON e.manager_id = o.emp_id
)
SELECT * FROM OrgChart ORDER BY level;

Output

Returns hierarchy levels from CEO down through managers

Free sign-in required · Google login available

Finished this lesson?

Mark it complete to track your progress.

❮ Previous

SQL LEAD and LAG

Next ❯

SQL Index Optimization

Stuck on something?

Ask the community in the CodesInTech forum.

Need a developer?

Learning on your own is great — and when you need professional help, our team builds custom software, websites and mobile apps for clients worldwide.

Explore Our Services

SQL Recursive CTE

💡 Key Takeaways: SQL Recursive CTE

SQL Recursive CTE Code Example

Key Takeaways: SQL Recursive CTE