If you're still writing CONNECT BY queries in Oracle because you think recursive CTEs aren't supported, it's time to update that assumption. Recursive CTEs have been available in Oracle since 11g Release 2, and are fully supported in every version since, including 19c and 23ai.

I still run into teams that don't know this. They'll write CONNECT BY out of habit, then struggle with cycle detection or portability issues that a recursive CTE would handle more cleanly. Both approaches work. But for new queries, it's worth knowing what you have available.

What Is a Recursive CTE?

A recursive CTE (Common Table Expression) is a WITH clause that references itself, allowing you to traverse hierarchical or graph-structured data iteratively. If you've used them in SQL Server or PostgreSQL, the syntax in Oracle is identical.

CONNECT BY: The Oracle Classic

Oracle's CONNECT BY syntax has been around since the early days and remains valid. For a simple employee-manager hierarchy: 

SELECT
    employee_id,
    manager_id,
    first_name,
    LEVEL AS depth
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

It works, it's readable, and Oracle DBAs know it well. But it comes with quirks, especially around cycle detection, filtering, and portability.

The Recursive CTE Equivalent

The same query written as a recursive CTE: 

WITH emp_hierarchy (employee_id, manager_id, first_name, depth) AS (
    -- Anchor: the root node(s)
    SELECT employee_id, manager_id, first_name, 1 AS depth
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive: join each level to the next
    SELECT e.employee_id, e.manager_id, e.first_name, h.depth + 1
    FROM employees e
    JOIN emp_hierarchy h ON e.manager_id = h.employee_id
)
SELECT * FROM emp_hierarchy;

The structure is explicit: an anchor member defines the starting rows, and the recursive member joins each iteration back to the CTE itself. Oracle handles the looping internally.

What I like about this approach is that the logic is visible. You can read it top to bottom and understand what it's doing. With CONNECT BY, the behavior of PRIOR and pseudo-columns like LEVEL can be opaque if you haven't worked with them recently.

Translating CONNECT BY Features

Several CONNECT BY pseudo-columns have recursive CTE equivalents:

  • LEVEL becomes an incremented depth counter in the recursive member.
  • CONNECT_BY_ROOT becomes carrying the root value down through each recursive step.
  • SYS_CONNECT_BY_PATH becomes building a path string using concatenation at each level.
  • CONNECT_BY_ISLEAF becomes a post-query check: rows with no children in the result set.
  • NOCYCLE becomes the CYCLE clause on the CTE to handle loops.

The translations aren't always one-liners, but they're straightforward once you understand the pattern.

When to Use Each

Stick with CONNECT BY if:

  • You rely heavily on SYS_CONNECT_BY_PATH and the team knows the syntax well.
  • The query is simple and already in production with no issues.
  • You're on a version older than 11gR2. Rare, but they exist.

Prefer recursive CTEs if:

  • You want SQL that's portable across Oracle, PostgreSQL, and SQL Server.
  • You need cleaner cycle detection with the CYCLE clause.
  • You're building complex multi-step logic where CTE readability helps.
  • Your team comes from a non-Oracle background.

There's no urgent reason to rewrite working CONNECT BY queries. They're not deprecated and they're not going anywhere. But for new hierarchical queries in Oracle 19c and beyond, recursive CTEs are a clean, portable, and fully supported choice. Use whichever fits the team and the task.