Querying Hierarchical Data Without Recursion: Using Self-Joins and Subqueries to Traverse Parent-Child Relationships

Introduction

You've just inherited a database that stores your company's entire organizational chart in a single employees table. Every employee row has a manager_id column that points to another row in the same table. Your stakeholder wants a report showing every employee alongside their manager's name, their manager's manager's name, and whether each person is a team lead, a director, or an individual contributor. The recursive CTE solution you've seen in tutorials would work — but your database is PostgreSQL 9.3, or you're on MySQL 5.6, or the DBA has specifically told you that the query planner on your analytical warehouse handles recursive queries poorly at scale. What now?

Hierarchical data — organization charts, product category trees, geographic region rollups, bill-of-materials structures, comment threads — is everywhere in production systems. Most SQL tutorials jump straight to WITH RECURSIVE as the canonical solution, which is fine when it's available and performant. But expert SQL practitioners know there's a rich toolkit for traversing parent-child relationships without recursion at all. Self-joins let you flatten known depth levels explicitly. Correlated subqueries let you navigate upward through a hierarchy on the fly. Nested subqueries and derived tables let you build multi-level rollups without a single recursive step. Understanding these non-recursive techniques makes you more dangerous in constrained environments and gives you a far deeper intuition for how hierarchical queries actually work under the hood.

By the end of this lesson you'll be able to model, query, and report on hierarchical data confidently — even in environments where recursion isn't available or isn't appropriate.

What you'll learn:

• How adjacency list tables store parent-child relationships and where the limits lie
• How to use self-joins to traverse a hierarchy of known depth
• How to use correlated subqueries to walk upward through a hierarchy dynamically
• How to combine subqueries and CASE expressions to classify nodes by their level or role
• Performance trade-offs, indexing strategies, and when non-recursive techniques genuinely beat recursive ones
• The nested set and closure table alternative models, and how pure SQL queries against them work without recursion at all

---

Prerequisites

This lesson assumes you're comfortable with the following:

• Writing multi-table queries using SQL JOINs, including LEFT JOIN and self-joins
• Using subqueries to filter and look up data, including correlated subqueries
• Writing SQL CASE expressions for conditional column logic
• Basic familiarity with aggregate functions and GROUP BY

If you've worked through the SQL Fundamentals path up to this point, you're ready. If any of those topics feel shaky, revisit them before continuing — this lesson moves fast.

---

The Adjacency List Model: What You're Working With

Before we can query hierarchical data intelligently, we need to understand the most common way it's stored. The adjacency list model is the default choice for 90% of developers building hierarchical features: each row stores a reference to its immediate parent.

Here's a realistic schema for a company's employee hierarchy:

CREATE TABLE employees (
    employee_id   INT          PRIMARY KEY,
    full_name     VARCHAR(100) NOT NULL,
    title         VARCHAR(100),
    department    VARCHAR(100),
    manager_id    INT          REFERENCES employees(employee_id),
    salary        NUMERIC(10,2)
);

And sample data representing four levels of depth:

INSERT INTO employees VALUES
  (1,  'Sandra Liu',       'CEO',                  'Executive',   NULL, 280000),
  (2,  'Marcus Webb',      'VP of Engineering',    'Engineering', 1,    195000),
  (3,  'Priya Nair',       'VP of Sales',          'Sales',       1,    185000),
  (4,  'Tobias Rehn',      'Director of Backend',  'Engineering', 2,    155000),
  (5,  'Ama Owusu',        'Director of Frontend', 'Engineering', 2,    150000),
  (6,  'Luis Ferreira',    'Director of Accounts', 'Sales',       3,    145000),
  (7,  'Keiko Tanaka',     'Senior Engineer',      'Engineering', 4,    115000),
  (8,  'Devon Clarke',     'Senior Engineer',      'Engineering', 4,    112000),
  (9,  'Fatima Al-Hassan', 'Engineer',             'Engineering', 5,    95000),
  (10, 'Yusuf Okafor',     'Account Executive',    'Sales',       6,    85000),
  (11, 'Rosa Delgado',     'Account Executive',    'Sales',       6,    82000),
  (12, 'Chen Wei',         'Engineer',             'Engineering', 5,    91000);

The hierarchy looks like this: Sandra (level 1) → Marcus and Priya (level 2) → Directors (level 3) → ICs (level 4).

The manager_id for Sandra is NULL because she has no manager. Everyone else points to the person directly above them.

This model is easy to write and easy to update. The problem comes when you need to read it in a way that reflects the hierarchical structure — because a single SELECT sees only rows, not relationships.

---

Level 1: The Basic Self-Join to Attach Parent Data

The most fundamental hierarchical query is the simplest one: show each employee alongside their direct manager's name. This is a self-join — joining the employees table to itself, treating one alias as the "child" and another as the "parent."

SELECT
    e.employee_id,
    e.full_name          AS employee_name,
    e.title              AS employee_title,
    m.full_name          AS manager_name,
    m.title              AS manager_title
FROM employees e
LEFT JOIN employees m
    ON e.manager_id = m.employee_id
ORDER BY e.employee_id;

Notice we're using a LEFT JOIN rather than an INNER JOIN. If we used INNER JOIN, Sandra — whose manager_id is NULL — would be dropped from the result. With LEFT JOIN, Sandra appears with NULL values in the manager columns, which is exactly right: she has no manager.

Result (abbreviated):

employee_id	employee_name	employee_title	manager_name	manager_title
1	Sandra Liu	CEO	NULL	NULL
2	Marcus Webb	VP of Engineering	Sandra Liu	CEO
4	Tobias Rehn	Director of Backend	Marcus Webb	VP of Engineering
7	Keiko Tanaka	Senior Engineer	Tobias Rehn	Director of Backend

This is the building block. But stakeholders rarely just want one level. They want the whole chain.

---

Level 2: Chaining Self-Joins for Known Depth

If your hierarchy has a known maximum depth — say, four levels — you can chain self-joins to flatten all four levels into a single query. This is the most readable and often the most performant approach for shallow, stable hierarchies.

SELECT
    l1.full_name  AS level_1,   -- CEO
    l2.full_name  AS level_2,   -- VP
    l3.full_name  AS level_3,   -- Director
    l4.full_name  AS level_4    -- IC / Senior
FROM employees l1
LEFT JOIN employees l2 ON l2.manager_id = l1.employee_id
LEFT JOIN employees l3 ON l3.manager_id = l2.employee_id
LEFT JOIN employees l4 ON l4.manager_id = l3.employee_id
WHERE l1.manager_id IS NULL   -- Start from the root
ORDER BY l2.full_name, l3.full_name, l4.full_name;

This query starts at the root (the CEO, identified by manager_id IS NULL) and walks down through four levels using three chained LEFT JOINs. Each join says: "find rows in the table whose manager_id matches the current level's employee_id."

Partial result:

level_1	level_2	level_3	level_4
Sandra Liu	Marcus Webb	Ama Owusu	Chen Wei
Sandra Liu	Marcus Webb	Ama Owusu	Fatima Al-Hassan
Sandra Liu	Marcus Webb	Tobias Rehn	Devon Clarke
Sandra Liu	Marcus Webb	Tobias Rehn	Keiko Tanaka
Sandra Liu	Priya Nair	Luis Ferreira	Rosa Delgado
Sandra Liu	Priya Nair	Luis Ferreira	Yusuf Okafor

What you're seeing is a denormalized view of the full hierarchy — each row is a complete path from root to leaf. This is extraordinarily useful for pivot tables, breadcrumb navigation, and audit reports.

Computing Level Numbers with CASE

You can augment any of the employee queries to classify nodes by their level using a CASE expression:

SELECT
    e.employee_id,
    e.full_name,
    e.title,
    CASE
        WHEN e.manager_id IS NULL THEN 'Level 1 - Executive'
        WHEN m.manager_id IS NULL THEN 'Level 2 - VP'
        WHEN gm.manager_id IS NULL THEN 'Level 3 - Director'
        ELSE 'Level 4 - Individual Contributor'
    END AS org_level
FROM employees e
LEFT JOIN employees m   ON e.manager_id   = m.employee_id
LEFT JOIN employees gm  ON m.manager_id   = gm.employee_id
ORDER BY e.employee_id;

Here m is the manager alias and gm is the "grandparent manager" alias. By inspecting whether each ancestor's manager_id is NULL, we can determine precisely how far down the chain an employee sits. This pattern is handy for role-based business rules — for example, flagging everyone at level 3 for a director bonus calculation.

---

Level 3: Walking Upward with Correlated Subqueries

Sometimes you don't need to walk down from the root — you need to walk up from a specific node. Correlated subqueries are the right tool here. A correlated subquery re-executes for each row in the outer query, which makes them perfect for asking "who is this person's manager's manager?"

SELECT
    e.employee_id,
    e.full_name,
    (
        SELECT m.full_name
        FROM employees m
        WHERE m.employee_id = e.manager_id
    ) AS direct_manager,
    (
        SELECT gm.full_name
        FROM employees gm
        WHERE gm.employee_id = (
            SELECT m2.manager_id
            FROM employees m2
            WHERE m2.employee_id = e.manager_id
        )
    ) AS skip_level_manager
FROM employees e
ORDER BY e.employee_id;

This query nests a correlated subquery inside another correlated subquery to retrieve the "skip-level manager" — two hops up the hierarchy. For Keiko Tanaka (employee 7), direct_manager would return Tobias Rehn, and skip_level_manager would return Marcus Webb.

Finding the Root Ancestor Without Recursion

Here's a trickier challenge: for any given employee, find the root ancestor (the person at the top of their chain) without using recursion. This is surprisingly doable for shallow trees using nested subqueries.

SELECT
    e.employee_id,
    e.full_name,
    COALESCE(
        (
            SELECT l1.full_name
            FROM employees l1
            WHERE l1.employee_id = (
                SELECT l2.manager_id
                FROM employees l2
                WHERE l2.employee_id = (
                    SELECT l3.manager_id
                    FROM employees l3
                    WHERE l3.employee_id = (
                        SELECT l4.manager_id
                        FROM employees l4
                        WHERE l4.employee_id = e.employee_id
                    )
                )
            )
            AND l1.manager_id IS NULL
        ),
        -- Fall through to shorter chains:
        (
            SELECT l1.full_name
            FROM employees l1
            WHERE l1.employee_id = (
                SELECT l2.manager_id
                FROM employees l2
                WHERE l2.employee_id = (
                    SELECT l3.manager_id
                    FROM employees l3
                    WHERE l3.employee_id = e.employee_id
                )
            )
            AND l1.manager_id IS NULL
        ),
        (
            SELECT m.full_name
            FROM employees m
            WHERE m.employee_id = e.manager_id
            AND m.manager_id IS NULL
        ),
        -- The employee IS the root
        CASE WHEN e.manager_id IS NULL THEN e.full_name END
    ) AS root_ancestor
FROM employees e
ORDER BY e.employee_id;

This is ugly but instructive. The COALESCE tries each depth-specific subquery in turn: "is the great-grandparent the root? if not, is the grandparent the root? if not, is the parent the root? if not, is this employee itself the root?" Only one of these will return a non-NULL value for any given row.

For production use at scale, this is more clearly expressed with a multi-step join approach. But this pattern reveals the underlying logic: you're asking "which ancestor at depth N has a NULL manager_id?" and working your way up from the deepest plausible depth.

---

Aggregating Across Hierarchy Levels

One of the most common hierarchical reporting tasks is roll-up aggregation: "what is the total salary budget under each VP?" This requires summing all descendants, not just direct reports. Here's how to do it with self-joins and subqueries for a three-level hierarchy (VP → Director → IC).

SELECT
    vp.employee_id,
    vp.full_name            AS vp_name,
    COUNT(DISTINCT dir.employee_id) AS direct_reports,
    COUNT(DISTINCT ic.employee_id)  AS total_ics,
    SUM(COALESCE(ic.salary, 0))     AS total_ic_payroll,
    SUM(COALESCE(dir.salary, 0))    AS director_payroll,
    SUM(COALESCE(ic.salary, 0))
        + SUM(COALESCE(dir.salary, 0)) AS total_org_payroll
FROM employees vp
LEFT JOIN employees dir ON dir.manager_id = vp.employee_id
LEFT JOIN employees ic  ON ic.manager_id  = dir.employee_id
WHERE vp.manager_id = (
    SELECT employee_id FROM employees WHERE manager_id IS NULL
)  -- Only VPs (direct reports of CEO)
GROUP BY vp.employee_id, vp.full_name
ORDER BY total_org_payroll DESC;

This query starts at the VP level (direct reports of the root), then fans out two levels down. By aggregating with COUNT(DISTINCT ...) and SUM(COALESCE(..., 0)), we get a complete payroll picture for each VP's organization.

Result:

vp_name	direct_reports	total_ics	total_ic_payroll	director_payroll	total_org_payroll
Marcus Webb	2	4	413000	305000	718000
Priya Nair	1	2	167000	145000	312000

This gives you a clear picture of organizational cost by division without a single recursive step.

---

Finding Siblings and Cousins

Another class of hierarchical query that people struggle with is lateral queries — finding all employees who share the same manager as a given employee (siblings), or who share the same grandparent (cousins in org-chart terms).

Finding Siblings

-- Find all colleagues who report to the same manager as Keiko Tanaka
SELECT
    sibling.employee_id,
    sibling.full_name,
    sibling.title
FROM employees sibling
WHERE sibling.manager_id = (
    SELECT manager_id
    FROM employees
    WHERE employee_id = 7  -- Keiko Tanaka
)
AND sibling.employee_id <> 7  -- Exclude Keiko herself
ORDER BY sibling.full_name;

This uses a subquery for filtering to dynamically identify Keiko's manager_id, then finds everyone else who shares it. Devon Clarke (ID 8) would appear here.

Finding Cousins (Same Grandparent)

-- Find everyone at the same "grandchild of CEO" level as Keiko Tanaka
SELECT
    cousin.employee_id,
    cousin.full_name,
    cousin.title,
    parent.full_name AS their_manager
FROM employees cousin
JOIN employees parent ON cousin.manager_id = parent.employee_id
WHERE parent.manager_id = (
    -- Get Keiko's grandparent's ID
    SELECT gp.manager_id
    FROM employees e
    JOIN employees gp ON e.manager_id = gp.employee_id
    WHERE e.employee_id = 7
)
AND cousin.employee_id <> 7
ORDER BY cousin.full_name;

This finds the grandparent via a join inside a scalar subquery, then returns everyone whose direct parent reports to that same grandparent. It's a "peer at the same depth" query.

---

The Exists-Based Approach: Checking Membership in a Subtree

Sometimes you don't want to retrieve the full hierarchy — you just want to know whether a given employee belongs to a particular subtree. For example: "Is this employee anywhere in Marcus Webb's organization?" This is a question about set membership, which makes EXISTS and NOT EXISTS natural fits.

-- Is employee 12 (Chen Wei) in Marcus Webb's (ID 2) org?
SELECT
    e.employee_id,
    e.full_name,
    CASE
        WHEN
            -- Direct report?
            e.manager_id = 2
            -- OR grandchild?
            OR e.manager_id IN (
                SELECT employee_id FROM employees WHERE manager_id = 2
            )
            -- OR great-grandchild?
            OR e.manager_id IN (
                SELECT employee_id FROM employees
                WHERE manager_id IN (
                    SELECT employee_id FROM employees WHERE manager_id = 2
                )
            )
        THEN 'Yes'
        ELSE 'No'
    END AS in_marcus_org
FROM employees e
WHERE e.employee_id = 12;

This is explicit depth enumeration using nested IN subqueries. For Chen Wei (ID 12), his manager is Ama Owusu (ID 5), whose manager is Marcus Webb (ID 2) — so he's a grandchild, and the second IN subquery catches him.

---

Advanced Pattern: Building a Path String

A very practical requirement in applications is displaying a breadcrumb path for each node: "Executive > Engineering > Backend > Senior Engineers". You can construct this with string concatenation in a chained self-join.

SELECT
    e.employee_id,
    e.full_name,
    CASE
        WHEN e.manager_id IS NULL
            THEN e.full_name
        WHEN m.manager_id IS NULL
            THEN m.full_name || ' > ' || e.full_name
        WHEN gm.manager_id IS NULL
            THEN gm.full_name || ' > ' || m.full_name || ' > ' || e.full_name
        ELSE
            ggm.full_name || ' > ' || gm.full_name || ' > '
            || m.full_name || ' > ' || e.full_name
    END AS org_path
FROM employees e
LEFT JOIN employees m   ON e.manager_id  = m.employee_id
LEFT JOIN employees gm  ON m.manager_id  = gm.employee_id
LEFT JOIN employees ggm ON gm.manager_id = ggm.employee_id
ORDER BY org_path;

For Keiko Tanaka, this produces: Sandra Liu > Marcus Webb > Tobias Rehn > Keiko Tanaka.

This is tremendously useful for CSV exports, reporting tools, and anywhere you need a human-readable chain of ownership. The CASE expression handles variable depth gracefully — employees at shallower levels simply hit an earlier branch and concatenate fewer segments.

---

Alternative Storage Models That Eliminate the Problem

If you have architectural influence over the schema, it's worth knowing that two alternative models for hierarchical data are queryable with pure non-recursive SQL and are dramatically faster for read-heavy workloads.

The Closure Table

The closure table model maintains a separate table that stores every ancestor-descendant pair in the hierarchy, including same-level pairs (where ancestor = descendant). This pre-computes the transitive closure of the parent-child relationship.

CREATE TABLE employee_hierarchy (
    ancestor_id   INT REFERENCES employees(employee_id),
    descendant_id INT REFERENCES employees(employee_id),
    depth         INT NOT NULL,  -- 0 = self, 1 = parent, 2 = grandparent...
    PRIMARY KEY (ancestor_id, descendant_id)
);

For our sample data, some rows would look like:

-- Sandra Liu (1) is ancestor of everyone, at various depths
(1, 1, 0),  -- Sandra is her own ancestor at depth 0
(1, 2, 1),  -- Sandra is Marcus's ancestor at depth 1
(1, 4, 2),  -- Sandra is Tobias's ancestor at depth 2
(1, 7, 3),  -- Sandra is Keiko's ancestor at depth 3
(2, 4, 1),  -- Marcus is Tobias's ancestor at depth 1
(2, 7, 2),  -- Marcus is Keiko's ancestor at depth 2
-- ... and so on for every pair

With this structure, "give me everyone in Marcus Webb's org" becomes a trivial single join:

SELECT e.*
FROM employees e
JOIN employee_hierarchy h
    ON h.descendant_id = e.employee_id
WHERE h.ancestor_id = 2  -- Marcus Webb
  AND h.depth > 0        -- Exclude Marcus himself
ORDER BY h.depth, e.full_name;

This is a single join with a highly selective index scan. It doesn't matter if the hierarchy is 4 levels or 40 — the query structure doesn't change. The trade-off is write complexity: every INSERT or UPDATE to the hierarchy must also maintain the closure table rows.

The Nested Set Model

The nested set model assigns each node a lft (left) and rgt (right) value such that every node's descendants have lft and rgt values that fall within the parent's range. A node is an ancestor of another if and only if ancestor.lft < descendant.lft AND ancestor.rgt > descendant.rgt.

CREATE TABLE employees_ns (
    employee_id INT PRIMARY KEY,
    full_name   VARCHAR(100),
    lft         INT NOT NULL,
    rgt         INT NOT NULL
);

-- Sandra Liu spans the entire tree
-- (1, 24) means she contains all values from 2 to 23
INSERT INTO employees_ns VALUES
  (1,  'Sandra Liu',       1,  24),
  (2,  'Marcus Webb',      2,  15),
  (4,  'Tobias Rehn',      3,  8),
  (7,  'Keiko Tanaka',     4,  5),
  (8,  'Devon Clarke',     6,  7),
  (5,  'Ama Owusu',        9,  14),
  (9,  'Fatima Al-Hassan', 10, 11),
  (12, 'Chen Wei',         12, 13),
  (3,  'Priya Nair',       16, 23),
  (6,  'Luis Ferreira',    17, 22),
  (10, 'Yusuf Okafor',     18, 19),
  (11, 'Rosa Delgado',     20, 21);

Finding all descendants of Marcus Webb:

SELECT descendant.*
FROM employees_ns AS ancestor
JOIN employees_ns AS descendant
    ON descendant.lft BETWEEN ancestor.lft AND ancestor.rgt
WHERE ancestor.employee_id = 2  -- Marcus Webb
  AND descendant.employee_id <> 2
ORDER BY descendant.lft;

Finding the full ancestry path (all ancestors of a given node):

SELECT ancestor.*
FROM employees_ns AS node
JOIN employees_ns AS ancestor
    ON node.lft BETWEEN ancestor.lft AND ancestor.rgt
WHERE node.employee_id = 7  -- Keiko Tanaka
  AND ancestor.employee_id <> 7
ORDER BY ancestor.lft;

Both queries are range-based, execute in O(log n) time with a proper index on (lft, rgt), and don't require recursion or fixed depth assumptions. The trade-off is that INSERTs and moves require renumbering large portions of the lft/rgt values — making writes expensive and complex.

---

Performance Considerations and Indexing

No matter which model you choose, the queries above are only as fast as your indexes allow. Here's what to index and why.

For the Adjacency List Model

The most important index is on manager_id:

CREATE INDEX idx_employees_manager_id ON employees(manager_id);

Every self-join ON condition hits manager_id. Without this index, each level of join is a full table scan — O(n²) for a two-level join, O(n³) for three levels. With the index, each level becomes an index lookup.

For queries that filter by manager and select multiple columns, a covering index improves performance further:

CREATE INDEX idx_employees_manager_covering
    ON employees(manager_id)
    INCLUDE (employee_id, full_name, title, salary);

More on indexing strategies at SQL Indexes Explained: How They Work and When to Create Them.

For the Closure Table

Index both columns of the composite primary key, and add separate indexes for querying by ancestor or descendant alone:

CREATE INDEX idx_hierarchy_ancestor   ON employee_hierarchy(ancestor_id, depth);
CREATE INDEX idx_hierarchy_descendant ON employee_hierarchy(descendant_id, depth);

The depth column in the index lets you efficiently find only direct children (depth = 1) or full subtrees (depth >= 1).

For the Nested Set

A composite index on (lft, rgt) is essential since range queries are the core of every nested set operation:

CREATE INDEX idx_employees_ns_lft_rgt ON employees_ns(lft, rgt);

---

Hands-On Exercise

Use the employees table defined at the start of this lesson to complete the following tasks. Write each as a standalone SQL query.

Exercise 1 — Direct Chain Report Write a query that returns each employee's name, their direct manager's name, and their skip-level manager's name (manager's manager). Use self-joins. Every employee should appear, including Sandra Liu (whose manager columns will be NULL).

Exercise 2 — Department Payroll Rollup Write a query that shows each VP (employees who report directly to the CEO), the number of directors under them, and the total annual salary cost of all employees in their organization (including directors and ICs, but not the VP themselves). Use chained LEFT JOINs and aggregate functions.

Exercise 3 — Level Classification Using CASE expressions and self-joins, write a query that assigns each employee to one of four labels: 'C-Suite', 'VP', 'Director', or 'Individual Contributor' based on their depth in the hierarchy. Output employee_id, full_name, and org_level.

Exercise 4 — Path String Write a query that produces a full org path string for each employee, from Sandra Liu at the root down to the employee, using the > separator. For Sandra herself, the path should just be her name. For Keiko Tanaka, it should be Sandra Liu > Marcus Webb > Tobias Rehn > Keiko Tanaka.

Exercise 5 — Closure Table Simulation Without creating any new tables, write a query using the employees table that returns all rows equivalent to what would be in a closure table for Marcus Webb's subtree — that is, show ancestor_id, descendant_id, and depth for Marcus and all of his descendants. Handle up to three levels of depth (depth 0 through 3). You may use UNION ALL.

Bonus Challenge Identify any employees whose salary is higher than their direct manager's salary. Return the employee's name, their salary, their manager's name, and their manager's salary.

-- Hint for the bonus challenge:
SELECT
    e.full_name       AS employee,
    e.salary          AS employee_salary,
    m.full_name       AS manager,
    m.salary          AS manager_salary
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id
WHERE e.salary > m.salary;

---

Common Mistakes & Troubleshooting

Mistake 1: Using INNER JOIN Instead of LEFT JOIN at the Root

The most common error beginners make is using INNER JOIN for the self-join and then wondering why the CEO (or top-level category, or root account) disappears from results. Always use LEFT JOIN when traversing upward from a node that might not have a parent.

Mistake 2: Off-by-One Depth in Chained Joins

When writing a four-level query, you need exactly three joins: l1→l2, l2→l3, l3→l4. It's easy to add one too many or one too few. Draw your hierarchy on paper first, count the levels, subtract one — that's the number of joins you need.

Mistake 3: Forgetting NULL Propagation in Aggregates

When using SUM() across LEFT JOINs, NULL values in the salary column of unmatched rows will propagate. Always use COALESCE(column, 0) inside aggregate functions when working with columns from the outer side of a LEFT JOIN. For a deep dive on this, see NULL Handling in SQL.

Mistake 4: Assuming Unique manager_id Values Are Guaranteed

In well-designed schemas with proper foreign keys and primary keys, every manager_id value points to exactly one employee. But in real-world data imports or legacy systems, duplicates and orphaned references exist. Before relying on scalar subqueries that assume one-to-one relationships, run a quick validation:

-- Check for orphaned manager references
SELECT e.employee_id, e.full_name, e.manager_id
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id
WHERE e.manager_id IS NOT NULL
  AND m.employee_id IS NULL;

-- Check for circular references (employee is their own manager)
SELECT employee_id, full_name
FROM employees
WHERE manager_id = employee_id;

Mistake 5: Querying a Depth-Unlimited Tree with Fixed-Depth Joins

If you write a three-join query and your product category tree has eight levels, your report silently omits five levels of categories. Build a defensive check:

-- Count maximum depth in an adjacency list (works up to 4 levels)
SELECT
    MAX(CASE
        WHEN lv3.manager_id IS NULL THEN 3
        WHEN lv4.manager_id IS NULL THEN 4
        ELSE 5  -- Flag: may be deeper than you can see
    END) AS estimated_max_depth
FROM employees lv1
LEFT JOIN employees lv2 ON lv2.manager_id = lv1.employee_id
LEFT JOIN employees lv3 ON lv3.manager_id = lv2.employee_id
LEFT JOIN employees lv4 ON lv4.manager_id = lv3.employee_id
WHERE lv1.manager_id IS NULL;

If this returns 5, you know there are nodes beyond your query's reach.

Mistake 6: Correlated Subqueries for Large Tables

Correlated subqueries re-execute once per outer row. For a 100-row employee table, that's fine. For a 500,000-row product catalog, scalar correlated subqueries can make a query run for minutes instead of seconds. When you notice a correlated subquery in a large-table context, refactor it into a JOIN:

-- Slow for large tables: correlated subquery
SELECT e.full_name,
       (SELECT m.full_name FROM employees m WHERE m.employee_id = e.manager_id)
FROM employees e;

-- Fast: equivalent LEFT JOIN
SELECT e.full_name, m.full_name AS manager_name
FROM employees e
LEFT JOIN employees m ON m.employee_id = e.manager_id;

---

Summary & Next Steps

You've covered a lot of ground. Let's consolidate what you've built:

The mental model: Every hierarchical query is fundamentally about traversing graph edges. The adjacency list stores one edge per row. Every additional level you want to traverse costs one more join or one more subquery nesting level. This is the core trade-off of the model.

Self-joins for known depth: Chaining LEFT JOIN aliases (l1, l2, l3, l4) is the cleanest and most readable approach for hierarchies with a bounded, known depth. Excellent for flattening, path building, and aggregation.

Correlated subqueries for upward traversal: When you need to climb from a leaf toward the root — or answer "does this node belong to that subtree?" — nested subqueries and scalar correlated lookups give you per-row flexibility.

Alternative models for performance: If you have schema control and a read-heavy workload, closure tables and nested sets eliminate the depth-dependency problem entirely and query in constant time regardless of tree depth.

Indexing always matters: Without an index on manager_id, hierarchical self-joins become catastrophically slow as tables grow. Always verify execution plans before deploying hierarchical queries to production.

Where to Go from Here

• For complex multi-step hierarchical reports, explore Advanced Subqueries and CTEs: Mastering Complex SQL Query Architecture — recursive CTEs are the natural next step after mastering the non-recursive techniques here.
• To understand how to structure multi-step analytical queries using derived tables and inline views, see Writing SQL FROM Scratch: Structuring Multi-Step Analytical Queries with Derived Tables and Inline Views.
• For full schema design guidance including hierarchical models, SQL Data Types and Schema Design Mastery covers constraints, normalization, and performance trade-offs at the architecture level.

The non-recursive techniques in this lesson are not workarounds or compromises — they're the foundational tools that make you understand why recursive SQL exists and what it's actually doing. Master these, and recursive CTEs will feel obvious rather than magical.