Skip to content

Lesson 15 ยท Joins, Subqueries and Hierarchies

Hierarchical Queries with CONNECT BY

Walk trees in Oracle with START WITH and CONNECT BY PRIOR, LEVEL, SYS_CONNECT_BY_PATH and ORDER SIBLINGS BY, and learn the recursive WITH equivalent you can run here.

Many real data sets are trees: an employee reports to a manager, a category contains subcategories, a part is built from smaller parts. Each row stores a pointer to its parent (manager_id, parent_id), and the question is "show me everyone under this person", at any depth. A plain join only goes one level; a hierarchical query walks the whole tree.

Oracle has had a dedicated syntax for this since version 2: START WITH and CONNECT BY. It is not part of the SQL standard, so this lesson shows it read-only. The standard alternative, a recursive WITH clause, has been in Oracle since 11gR2, and runs in the editor.

flowchart TB
  CEO["Kofi Okafor<br/>CEO"] --> H1["Yara Demir<br/>Engineering"]
  CEO --> H2["Farah Tanaka<br/>Design"]
  H1 --> E1["Ivo Rossi"]
  H1 --> E2["Wen Weber"]
  H2 --> D1["Wen Kaya"]

Each row points at its parent; the root has no parent.

START WITH and CONNECT BY PRIOR

SQL
SELECT employee_id, last_name, manager_id, LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

Read it like this:

  • START WITH picks the root rows, the starting points of the walk.
  • CONNECT BY PRIOR parent_col = child_col says how to get from one row to the next. PRIOR refers to the row you just came from (the parent). Here, a row's manager_id must equal the previous row's employee_id.
  • LEVEL is a pseudocolumn: 1 for the roots, 2 for their children, and so on.

Direction matters. To go upwards, from an employee to the CEO, swap the sides:

SQL
SELECT employee_id, last_name, LEVEL
FROM employees
START WITH employee_id = 112
CONNECT BY PRIOR manager_id = employee_id;

Oracle returns the rows depth first: a parent, then its first child and that child's descendants, then the next child. This is the natural order for printing an outline.

Making the tree readable

Indent by level with LPAD:

SQL
SELECT LPAD(' ', 2 * (LEVEL - 1)) || last_name AS org_chart, LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id
ORDER SIBLINGS BY last_name;

ORDER SIBLINGS BY sorts the children of each parent without breaking the tree. A normal ORDER BY would scramble the depth-first order.

Hierarchical pseudocolumns and operators

Item Meaning
LEVEL Depth from the root (root = 1)
PRIOR expr Value of expr in the parent row
CONNECT_BY_ROOT expr Value of expr in the root row of this branch
CONNECT_BY_ISLEAF 1 if the row has no children
CONNECT_BY_ISCYCLE 1 if the row loops back (needs NOCYCLE)
SYS_CONNECT_BY_PATH(col, '/') The path from the root, such as /Okafor/Demir/Rossi
SQL
SELECT last_name,
       SYS_CONNECT_BY_PATH(last_name, '/') AS path,
       CONNECT_BY_ROOT last_name           AS top_boss,
       CONNECT_BY_ISLEAF                   AS is_leaf
FROM employees
START WITH manager_id IS NULL
CONNECT BY NOCYCLE PRIOR employee_id = manager_id;

NOCYCLE tells Oracle to stop when it meets a row it has already visited. Without it, a data error such as A reporting to B and B reporting to A ends the query with ORA-01436: CONNECT BY loop in user data.

A WHERE clause filters rows after the tree is built, while a condition in CONNECT BY stops the walk at that branch:

SQL
-- prune the whole subtree under a department head
CONNECT BY PRIOR employee_id = manager_id AND last_name <> 'Demir'

You can also aggregate over a tree, for example to count the people in each manager's whole organisation, with CONNECT_BY_ROOT:

SQL
SELECT CONNECT_BY_ROOT last_name AS manager, COUNT(*) - 1 AS people_below
FROM employees
CONNECT BY PRIOR employee_id = manager_id
GROUP BY CONNECT_BY_ROOT last_name;

Leaving out START WITH makes every row a root, so every subtree is walked.

The recursive WITH clause (runnable)

The standard way to write the same query is a recursive common table expression. It has two parts joined by UNION ALL: an anchor (the starting rows) and a recursive member that joins the previous result to the table. In Oracle the column list after the CTE name is required and the word RECURSIVE is not used; in the editor (SQLite) you write WITH RECURSIVE. Everything else is the same.

SQL
-- Oracle
WITH tree (employee_id, last_name, manager_id, lvl) AS (
  SELECT employee_id, last_name, manager_id, 1 FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.employee_id, e.last_name, e.manager_id, t.lvl + 1
  FROM employees e JOIN tree t ON e.manager_id = t.employee_id
)
SELECT * FROM tree;

Stand-in for START WITH ... CONNECT BY PRIOR that you can run:

SQL Stand-in: the org chart as a recursive CTE ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The lvl column plays the role of LEVEL, and ordering by the accumulated sort_path gives the same depth-first order as ORDER SIBLINGS BY last_name.

The path and root come out of the recursion too, which is the stand-in for SYS_CONNECT_BY_PATH and CONNECT_BY_ROOT:

SQL Stand-in: path and root

Press Run (or Ctrl + Enter) to see the result here.

Going upwards works the same way: start from one employee and join on e.id = t.manager_id:

SQL Stand-in: the chain of command upwards ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

CONNECT BY without a tree

Because CONNECT BY LEVEL <= n keeps producing rows until the level reaches n, Oracle programmers use it to generate sequences from DUAL:

SQL
SELECT LEVEL AS n FROM dual CONNECT BY LEVEL <= 5;          -- 1 2 3 4 5
SELECT DATE '2024-01-01' + LEVEL - 1 AS day
FROM dual CONNECT BY LEVEL <= 7;                              -- a week of dates

The stand-in is again a recursive CTE:

SQL Stand-in: generating numbers

Press Run (or Ctrl + Enter) to see the result here.

Which should you use?

CONNECT BY is shorter, has handy pseudocolumns, and is fast on Oracle. Recursive WITH is standard, supports breadth-first search and cycle detection with the SEARCH and CYCLE clauses, and is portable to PostgreSQL, SQL Server and MySQL. Read both; write whichever your team uses.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. In CONNECT BY PRIOR employee_id = manager_id, what does PRIOR refer to?

    PRIOR evaluates its expression on the parent row, so a row is a child when its manager_id equals the parent's employee_id.
  2. 2. How do you walk a tree upwards from a child to the root?

    Putting PRIOR on the parent-pointer column makes Oracle follow each row's pointer to its parent.
  3. 3. What does ORDER SIBLINGS BY do?

    A normal ORDER BY breaks the depth-first hierarchy, while ORDER SIBLINGS BY sorts rows only among siblings.
  4. 4. Which error appears when the data contains a loop and you did not write NOCYCLE?

    Oracle detects the loop and stops with ORA-01436. NOCYCLE makes it skip rows already visited.
  5. 5. What are the two parts of a recursive WITH clause joined by UNION ALL?

    The anchor returns the starting rows; the recursive member joins the CTE to the table to find the next level.

Summary

  • START WITH chooses the roots and CONNECT BY PRIOR parent = child defines how rows link.
  • LEVEL, SYS_CONNECT_BY_PATH, CONNECT_BY_ROOT and CONNECT_BY_ISLEAF describe the position of each row.
  • ORDER SIBLINGS BY sorts within a branch; NOCYCLE guards against bad data.
  • Recursive WITH is the standard equivalent and runs on every major database.
  • CONNECT BY LEVEL <= n FROM dual is the classic Oracle number generator.

Next: PL/SQL Blocks and Variables