Skip to content
Tutorials Center
My learning

Topics

Lesson 15 of 38 ยท 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