Skip to content
  • Home
  • About
  • No Access
  • Privacy Policy
Bits of .NET
Bits of .NET
  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy

Bits of .NET

Daily micro-tips for C#, SQL, performance, and scalable backend engineering.

  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy
SQL

SQL: Use Recursive CTE to Query Hierarchical Data (Employee Trees)

- 29.05.26 - ErcanOPAK

🌳 Self-Joins Are Painful. CTE Makes It Easy.

Org charts, categories, comments, folder trees — all hierarchical. Recursive CTEs query parent-child relationships elegantly.

📝 Employee Hierarchy

-- Table structure
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    manager_id INT REFERENCES employees(id)
);

-- Recursive CTE to get org tree
WITH RECURSIVE org_tree AS (
    -- Anchor: Top-level managers
    SELECT 
        id, 
        name, 
        manager_id, 
        0 AS level,
        CAST(name AS VARCHAR(500)) AS path
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- Recursive: Employees under each manager
    SELECT 
        e.id, 
        e.name, 
        e.manager_id, 
        ot.level + 1,
        ot.path || ' → ' || e.name
    FROM employees e
    JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT 
    id, 
    REPEAT('  ', level) || name AS indented_name,
    level,
    path
FROM org_tree
ORDER BY path;

🎯 Category Tree with Breadcrumbs

WITH RECURSIVE category_tree AS (
    SELECT 
        id, 
        name, 
        parent_id,
        name AS breadcrumb
    FROM categories
    WHERE parent_id IS NULL
    
    UNION ALL
    
    SELECT 
        c.id, 
        c.name, 
        c.parent_id,
        ct.breadcrumb || ' > ' || c.name
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT id, breadcrumb
FROM category_tree
ORDER BY breadcrumb;

-- Output:
-- Electronics > Computers > Laptops
-- Electronics > Computers > Desktops
-- Electronics > Phones > Smartphones
-- Clothing > Men > Shirts

✅ Find All Subordinates

-- Get entire team under specific manager
WITH RECURSIVE team AS (
    SELECT id, name, manager_id
    FROM employees
    WHERE id = 123  -- Manager ID
    
    UNION ALL
    
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    JOIN team t ON e.manager_id = t.id
)
SELECT * FROM team
ORDER BY manager_id, name;

-- Count subordinates per manager
WITH RECURSIVE hierarchy AS (
    SELECT id, 0 AS depth
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    SELECT e.id, h.depth + 1
    FROM employees e
    JOIN hierarchy h ON e.manager_id = h.id
)
SELECT 
    m.name AS manager,
    COUNT(e.id) AS subordinates,
    MAX(e.depth) AS max_depth
FROM employees m
LEFT JOIN hierarchy e ON m.id = e.id
WHERE e.depth > 0
GROUP BY m.id, m.name;

💡 Performance Tips

  • Add indexes on parent_id columns
  • Limit recursion depth with WHERE clause
  • Use materialized path for frequent queries
  • Consider closure table for complex trees
  • Set max_recursion_depth if needed

“Nested set model was too complex. Recursive CTE replaced 100 lines of code with 20 lines. Employee tree now loads in 50ms. Simple and maintainable.”

— Database Developer

Related posts:

How to generate a random number for each row in T-SQL

How to generate a random number for each row in T-SQL

SQL Temporal Tables: Track Data Changes Over Time

SQL Temporal Tables: Track Data Changes Over Time

SQL Server: Why Rebuilding a Clustered Index Doesn't Fix Fragmented Non-Clustered Indexes

SQL Server: Why Rebuilding a Clustered Index Doesn't Fix Fragmented Non-Clustered Indexes

Views: 17

Post navigation

.NET Core: Use Problem Details for Standardized API Error Responses
C#: Use Caller Info Attributes for Better Logging Without Reflection

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

November 2026
M T W T F S S
 1
2345678
9101112131415
16171819202122
23242526272829
30  
« Oct    

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,055)
  • Get the User Name and Domain Name from an Email Address in SQL (980)
  • How to add default value for Entity Framework migrations for DateTime and Bool (977)
  • Get the First and Last Word from a String or Sentence in SQL (858)
  • How to select distinct rows in a datatable in C# (854)
  • How to enable, disable and check if Service Broker is enabled on a database in SQL Server (653)
  • Add Constraint to SQL Table to ensure email contains @ (609)
  • Average of all values in a column that are not zero in SQL (570)
  • How to use Map Mode for Vertical Scroll Mode in Visual Studio (547)
  • Find numbers with more than two decimal places in SQL (475)

Recent Posts

  • C#: Fix a ConditionalWeakTable Entry Vanishing Too Soon
  • Docker: Fix a Stale COPY Layer After Editing .dockerignore
  • Photoshop: Fix a Pattern Overlay Scaling Wrong on a High-DPI File
  • SQL Server: Fix a Computed Column Index Skipped by Implicit Cast
  • HTML5: Fix a required Input Inside a Hidden Tab Blocking Submit
  • SQL Server: Fix ROW_NUMBER Ties Reordering Between Identical Runs
  • C#: Fix an Enum HasFlag Returning True for the Zero Value
  • WordPress: Fix Featured Images Vanishing After a CDN Migration
  • Photoshop: Fix Content-Aware Fill Smearing a Repeating Pattern
  • Asp.Net Core: Fix Antiforgery Tokens Rejected Behind a Reverse Proxy

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,055)
  • Get the User Name and Domain Name from an Email Address in SQL (980)
  • How to add default value for Entity Framework migrations for DateTime and Bool (977)
  • Get the First and Last Word from a String or Sentence in SQL (858)
  • How to select distinct rows in a datatable in C# (854)

Recent Posts

  • C#: Fix a ConditionalWeakTable Entry Vanishing Too Soon
  • Docker: Fix a Stale COPY Layer After Editing .dockerignore
  • Photoshop: Fix a Pattern Overlay Scaling Wrong on a High-DPI File
  • SQL Server: Fix a Computed Column Index Skipped by Implicit Cast
  • HTML5: Fix a required Input Inside a Hidden Tab Blocking Submit

Social

  • ErcanOPAK.com
  • GoodReads
  • LetterBoxD
  • Linkedin
  • The Blog
  • Twitter
© 2026 Bits of .NET | Built with Xblog Plus free WordPress theme by wpthemespace.com