Hierarchical Queries with CONNECT BY in Oracle SQL

The hierarchical data problem

Many business structures are trees: organisation charts (employee → manager), product categories (parent → child), project tasks (dependency chains), geographical hierarchies (country → state → city).

Oracle’s CONNECT BY handles these naturally.

Basic syntax

SELECT
  employee_id,
  last_name,
  manager_id,
  LEVEL
FROM employees
START WITH manager_id IS NULL     -- root of the tree
CONNECT BY PRIOR employee_id = manager_id  -- parent-child relationship
ORDER SIBLINGS BY last_name;      -- sort within each level

LEVEL is a pseudocolumn returning 1 for root, 2 for its children, etc.

Indented display

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

SYS_CONNECT_BY_PATH: full ancestry path

SELECT
  employee_id,
  last_name,
  SYS_CONNECT_BY_PATH(last_name, ' → ') AS hierarchy_path
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;
-- Result: → King → De Haan → Hunold → Ernst

CONNECT_BY_ROOT: finding the root

SELECT
  employee_id,
  last_name,
  CONNECT_BY_ROOT last_name AS top_manager
FROM employees
CONNECT BY PRIOR employee_id = manager_id
START WITH manager_id IS NULL;

Detecting cycles

Data errors can create circular references. Use NOCYCLE and CONNECT_BY_ISCYCLE:

SELECT employee_id, last_name, CONNECT_BY_ISCYCLE AS is_cycle
FROM employees
START WITH manager_id IS NULL
CONNECT BY NOCYCLE PRIOR employee_id = manager_id;

Recursive CTEs (modern alternative)

Oracle 11g R2+ supports recursive CTEs as an alternative:

WITH org AS (
  SELECT employee_id, last_name, manager_id, 1 AS lvl FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.employee_id, e.last_name, e.manager_id, o.lvl + 1
  FROM employees e JOIN org o ON e.manager_id = o.employee_id
)
SELECT * FROM org ORDER BY lvl;

Recursive CTEs are portable to other databases; CONNECT BY is Oracle-specific but often more readable for Oracle teams.