Hierarchies: recursive CTEs and Oracle CONNECT BY

SQL Server and Oracle both walk a hierarchy with a recursive CTE, written without the RECURSIVE keyword that PostgreSQL and SQLite accept: WITH org (id, name, lvl) AS (anchor UNION ALL recursive member). Oracle also has an older syntax of its own for the same walk. In Oracle CONNECT BY queries, START WITH picks the roots, CONNECT BY PRIOR id = manager_id links each row to its parent, and LEVEL, SYS_CONNECT_BY_PATH, CONNECT_BY_ROOT and CONNECT_BY_ISLEAF describe each row’s place in the tree. Oracle CONNECT BY is common in older Oracle code; recursive CTEs are the form to write when a query must run on both engines. The engines then differ in what stops a query: SQL Server ends a recursion after 100 levels unless told otherwise, Oracle insists on the column list, and only Oracle has clauses that order the walk and mark loops.

The tree used in this lesson

Ten people, one row each, with manager_id pointing to the person above. Asha has no manager, so she is the root. LEVEL counts from 1 at the root, and a leaf is a row with no reports.

One walk, written for both engines

-- One reporting tree, walked with the recursive CTE form that SQL Server and Oracle both document: a column list,
-- no RECURSIVE keyword, an anchor and a recursive member joined by UNION ALL. (T-SQL builds the path with + or
-- CONCAT instead of ||, before SQL Server 2025.) The columns stand in for Oracle's CONNECT BY features:
--   lvl     LEVEL                       path     SYS_CONNECT_BY_PATH(name, '/')
--   root    CONNECT_BY_ROOT name        is_leaf  CONNECT_BY_ISLEAF
-- Oracle's CONNECT BY version of the same walk would read:
--   SELECT id, name, LEVEL, SYS_CONNECT_BY_PATH(name, '/'), CONNECT_BY_ROOT name, CONNECT_BY_ISLEAF
--   FROM employees START WITH manager_id IS NULL CONNECT BY PRIOR id = manager_id ORDER SIBLINGS BY name;
CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT NOT NULL, manager_id INTEGER REFERENCES employees (id));
INSERT INTO employees (id, name, manager_id) VALUES
  (1, 'Asha', NULL),
  (2, 'Ravi', 1),   (3, 'Fatima', 1),
  (4, 'Kabir', 2),  (5, 'Meera', 2),  (6, 'Imran', 3),
  (7, 'Zoya', 4),   (8, 'Neha', 4),   (9, 'Tenzin', 5), (10, 'Arjun', 6);

WITH org (id, name, manager_id, lvl, path, root) AS (
  SELECT id, name, manager_id, 1, '/' || name, name
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, o.lvl + 1, o.path || '/' || e.name, o.root
  FROM employees AS e
  JOIN org AS o ON e.manager_id = o.id
)
SELECT id,
       substr('          ', 1, 2 * (lvl - 1)) || name                              AS chart,
       lvl,
       path,
       root,
       CASE WHEN EXISTS (SELECT 1 FROM employees AS c WHERE c.manager_id = org.id) THEN 0 ELSE 1 END AS is_leaf
FROM org
ORDER BY path;

┌────┬──────────────┬─────┬──────────────────────────┬──────┬─────────┐
│ id │    chart     │ lvl │           path           │ root │ is_leaf │
├────┼──────────────┼─────┼──────────────────────────┼──────┼─────────┤
│ 1  │ Asha         │ 1   │ /Asha                    │ Asha │ 0       │
│ 3  │   Fatima     │ 2   │ /Asha/Fatima             │ Asha │ 0       │
│ 6  │     Imran    │ 3   │ /Asha/Fatima/Imran       │ Asha │ 0       │
│ 10 │       Arjun  │ 4   │ /Asha/Fatima/Imran/Arjun │ Asha │ 1       │
│ 2  │   Ravi       │ 2   │ /Asha/Ravi               │ Asha │ 0       │
│ 4  │     Kabir    │ 3   │ /Asha/Ravi/Kabir         │ Asha │ 0       │
│ 8  │       Neha   │ 4   │ /Asha/Ravi/Kabir/Neha    │ Asha │ 1       │
│ 7  │       Zoya   │ 4   │ /Asha/Ravi/Kabir/Zoya    │ Asha │ 1       │
│ 5  │     Meera    │ 3   │ /Asha/Ravi/Meera         │ Asha │ 0       │
│ 9  │       Tenzin │ 4   │ /Asha/Ravi/Meera/Tenzin  │ Asha │ 1       │
└────┴──────────────┴─────┴──────────────────────────┴──────┴─────────┘

The anchor selects the root, WHERE manager_id IS NULL, at level 1 with a path of /Asha. Each pass of the recursive member joins employees to the rows found so far on e.manager_id = o.id, adds 1 to the level and appends the name to the path. The walk stops when a pass finds no new rows. Sorting by the path puts every manager directly above their team, with siblings in alphabetical order: the order ORDER SIBLINGS BY name gives in Oracle.

Run in Oracle, the CONNECT BY query in the example’s comment returns the same ten rows in the same order. The difference is who does the work: CONNECT BY computes the level, the path and the leaf flag for you, while the CTE carries them as columns you build yourself. The CTE is longer but explicit, and of the two forms it is the only one SQL Server can run.

Three details make the same text acceptable to both engines. The column list after the CTE’s name is optional in SQL Server but required in Oracle for a recursive CTE, so always write it. UNION ALL is the only operator either engine allows between the anchor and the recursive member. And the path is built with ||, which Oracle has always had; T-SQL uses + or CONCAT, and SQL Server 2025 adds || too.

CONNECT BY, clause by clause

PRIOR marks the parent’s side of the condition, so PRIOR id = manager_id reads “the parent’s id equals this row’s manager_id”. Without START WITH, every row starts a walk of its own, so each person appears once as the root of their own walk and once more under every manager above them.

When to keep CONNECT BY

Rewriting working Oracle code is not always worth it. CONNECT BY is often shorter, and existing reports depend on its exact order of rows. Rewrite when the query must also run on SQL Server or PostgreSQL, or when it needs what only the recursive form offers: Oracle’s reference notes that recursive CTEs add depth-first and breadth-first search and several recursive branches.

What stops a hierarchy query

• A loop in the data fails a recursive CTE without a guard, and fails CONNECT BY with ORA-01436.
• The CYCLE clause of Oracle’s recursive CTE marks the row that closes a loop instead of failing.
• NOCYCLE lets CONNECT BY return rows despite a loop, and CONNECT_BY_ISCYCLE shows where it is.
• MAXRECURSION is SQL Server’s limit on recursion levels, 100 unless a query sets another.
• A filter’s position decides whether it removes whole branches or single rows.
• GENERATE_SERIES returns a series of numbers in SQL Server 2022 and later, without a recursive CTE.

Your turn

-- Rewrite the CONNECT BY report as a recursive CTE: id, name, lvl and path for everyone.
-- This starter finds only the people at the top.
WITH org (id, name, lvl, path) AS (
  SELECT id, name, 1, '/' || name
  FROM employees
  WHERE manager_id IS NULL
)
SELECT id, name, lvl, path
FROM org;

# Sample tests: each one builds a small employees table, runs your query on it and compares the rows it returns (in
# any order).
tests:
  - name: a three-level tree
    setup: |
      CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT NOT NULL, manager_id INTEGER);
      INSERT INTO employees VALUES
        (1, 'Asha', NULL), (2, 'Ravi', 1), (3, 'Fatima', 1), (4, 'Kabir', 2);
    expected:
      columns: [id, name, lvl, path]
      rows:
        - [1, Asha, 1, /Asha]
        - [2, Ravi, 2, /Asha/Ravi]
        - [3, Fatima, 2, /Asha/Fatima]
        - [4, Kabir, 3, /Asha/Ravi/Kabir]
  - name: two people at the top start two trees
    setup: |
      CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT NOT NULL, manager_id INTEGER);
      INSERT INTO employees VALUES
        (10, 'Meera', NULL), (11, 'Imran', NULL), (12, 'Zoya', 10), (13, 'Neha', 11), (14, 'Tenzin', 13);
    expected:
      columns: [id, name, lvl, path]
      rows:
        - [10, Meera, 1, /Meera]
        - [11, Imran, 1, /Imran]
        - [12, Zoya, 2, /Meera/Zoya]
        - [13, Neha, 2, /Imran/Neha]
        - [14, Tenzin, 3, /Imran/Neha/Tenzin]
  - name: a chain five levels deep
    setup: |
      CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT NOT NULL, manager_id INTEGER);
      INSERT INTO employees VALUES
        (1, 'Arjun', NULL), (2, 'Kavya', 1), (3, 'Rohan', 2), (4, 'Sneha', 3), (5, 'Vikram', 4);
    expected:
      columns: [id, name, lvl, path]
      rows:
        - [1, Arjun, 1, /Arjun]
        - [2, Kavya, 2, /Arjun/Kavya]
        - [3, Rohan, 3, /Arjun/Kavya/Rohan]
        - [4, Sneha, 4, /Arjun/Kavya/Rohan/Sneha]
        - [5, Vikram, 5, /Arjun/Kavya/Rohan/Sneha/Vikram]

Cycles and depth limits in both engines, with the failures reproduced Pro: opens with a Pro, Premium or Ultimate pass

Filters, sibling order and series translated between the syntaxes, worked through Pro: opens with a Pro, Premium or Ultimate pass

Interview questions

Key takeaways

• Write recursive CTEs with a column list, without RECURSIVE, and with UNION ALL: both engines accept that form.
• CONNECT BY PRIOR id = manager_id links parent to child; LEVEL is 1 at the root.
• SQL Server stops a recursion after 100 levels by default; set MAXRECURSION and keep a cycle guard.
• Oracle reports loops with ORA-01436, or marks them with NOCYCLE or the CYCLE clause.