How to Use CONNECT BY in Oracle
Last updated August 25, 2026 · By the SaturnSQL team
CONNECT BY PRIOR with START WITH walks a hierarchy such as an employee-manager tree. LEVEL and SYS_CONNECT_BY_PATH expose depth and path.
SELECT employee_id,
manager_id,
name,
LEVEL AS depth,
SYS_CONNECT_BY_PATH(name, '/') AS org_path
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;How the clauses fit together
START WITH picks the root row(s), here the employees with no manager. CONNECT BY PRIOR employee_id = manager_id tells Oracle how to find each row's children: PRIOR marks which side of the condition refers to the parent row already visited. LEVEL is a pseudo-column giving the depth of each row in the tree, starting at 1 for the roots.
Breaking cycles
Bad data can create a cycle, such as an employee who is their own manager two levels up, which makes a plain CONNECT BY raise ORA-01436. CONNECT BY NOCYCLE lets the query finish anyway, skipping the row that would repeat the cycle; add CONNECT_BY_ISCYCLE to a SELECT list to flag which rows it stopped at.
SELECT employee_id, name, LEVEL, CONNECT_BY_ISCYCLE
FROM employees
START WITH manager_id IS NULL
CONNECT BY NOCYCLE PRIOR employee_id = manager_id;CONNECT BY is Oracle-specific syntax; the portable equivalent is a recursive WITH clause (WITH cte (id, ...) AS (SELECT ... UNION ALL SELECT ... FROM t JOIN cte)), supported since Oracle 11g Release 2, which also works on databases that never implemented CONNECT BY. Oracle requires that explicit column alias list on a recursive CTE, raising ORA-32039 if it's omitted.
Run this in SaturnSQL
SaturnSQL is a browser-based SQL editor for teams: shared query library, schema-aware autocomplete, and scheduled exports to Google Sheets and Slack.
Try it free