The Adjacency List Problem#

In retail and manufacturing, data is rarely flat.

• An Employee reports to a Manager, who reports to a Director, who reports to the CEO.
• A Bicycle is built from a Frame, two Wheels, and a Drivetrain. A Wheel is built from a Rim, a Tire, and Spokes.

This is typically stored in an Adjacency List Model: a table with an ID and a PARENT_ID.

If you want to find the CEO, it's easy: WHERE MANAGER_ID IS NULL. If you want to find Bob's direct reports, it's easy: WHERE MANAGER_ID = 2. But how do you find all employees under Alice, across all levels of the company? A standard SQL JOIN cannot do this because you don't know how deep the tree goes.

Syntax Breakdown#

Oracle introduced the START WITH ... CONNECT BY syntax specifically to traverse adjacency lists recursively.

SELECT 
  emp_id, 
  name, 
  manager_id
FROM employees
START WITH manager_id IS NULL       -- 1. Where do we start?
CONNECT BY PRIOR emp_id = manager_id; -- 2. How do we move down?

1. START WITH

This clause identifies the root node(s) of your tree. You can start at the top (MANAGER_ID IS NULL), or you can start in the middle (e.g., START WITH emp_id = 2 to only get Bob's sub-tree).

2. CONNECT BY PRIOR

This is the recursion engine. PRIOR is a keyword that refers to the parent row. PRIOR emp_id = manager_id reads as: "Take the emp_id of the row I am currently evaluating (the parent), and find all rows where the manager_id equals this value (the children)."

Hierarchical Pseudocolumns#

Oracle provides several magical "pseudocolumns" that are only available during a hierarchical query.

LEVEL

Returns an integer representing the depth of the node in the tree. The root is LEVEL 1. We can use this to visually indent our output!

SELECT 
  LPAD(' ', 2 * (LEVEL - 1)) || name AS indented_name,
  LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

CONNECT_BY_ISLEAF

Returns 1 if the current row has no children, and 0 if it has children. This is vital in a Bill of Materials (BOM) to find the absolute base components that cannot be broken down further.

SYS_CONNECT_BY_PATH

A function that builds a delimited string representing the path from the root to the current node.

SELECT 
  name,
  SYS_CONNECT_BY_PATH(name, '/') AS hierarchy_path
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

-- Output for Charlie: /CEO Alice/VP Bob/Dev Charlie

Ordering Siblings#

If you apply a standard ORDER BY name to a hierarchical query, it will destroy the tree structure and sort the entire result set alphabetically.

To sort nodes within their specific level and parent, you must use ORDER SIBLINGS BY.

SELECT name, LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id
ORDER SIBLINGS BY name ASC;

Alternative: Recursive CTEs (ANSI Standard)#

CONNECT BY is strictly Oracle proprietary syntax. If you ever migrate to PostgreSQL or SQL Server, you must rewrite your queries using the ANSI standard Recursive Common Table Expressions (CTE).

A Recursive CTE uses a WITH clause that unions a base query (the root) with a recursive query.

WITH employee_tree (emp_id, name, manager_id, lvl) AS (
  -- Anchor Member (Root)
  SELECT emp_id, name, manager_id, 1
  FROM employees
  WHERE manager_id IS NULL
  
  UNION ALL
  
  -- Recursive Member (Children)
  SELECT child.emp_id, child.name, child.manager_id, parent.lvl + 1
  FROM employees child
  JOIN employee_tree parent ON child.manager_id = parent.emp_id
)
SELECT * FROM employee_tree;

While Recursive CTEs are cross-platform, Oracle's CONNECT BY is often more concise and easier to read for complex path-building tasks.

Common Gotchas#

Key Takeaways#