Recursive CTE (WITH RECURSIVE)

Oracle

-- Hierarchy with CONNECT BY:
SELECT id, name, manager_id, LEVEL
FROM employee
START WITH manager_id IS NULL
CONNECT BY PRIOR id = manager_id;

-- Alternative (11gR2+): standard recursive WITH (the RECURSIVE keyword is not written)
WITH tree (id, name, manager_id) AS (
  SELECT id, name, manager_id FROM employee WHERE manager_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.manager_id
  FROM employee c JOIN tree a ON c.manager_id = a.id
)
SELECT * FROM tree;

Used to traverse hierarchical (tree) data: org charts, bills of materials and the like. Oracle's classic way is CONNECT BY / PRIOR and the LEVEL pseudocolumn; START WITH sets the root row. Since 11gR2 the standard recursive CTE is supported as well.

PostgreSQL

WITH RECURSIVE tree (id, name, manager_id, level) AS (
  SELECT id, name, manager_id, 1
  FROM employee WHERE manager_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.manager_id, a.level + 1
  FROM employee c JOIN tree a ON c.manager_id = a.id
)
SELECT * FROM tree ORDER BY level;

WITH RECURSIVE combines an anchor query and a recursive part with UNION ALL; the recursive part runs repeatedly until the result is empty. Ideal for hierarchies and graph traversal. Guard against infinite loops with UNION (instead of ALL) or cycle protection.

MySQL

WITH RECURSIVE tree (id, name, manager_id, level) AS (
  SELECT id, name, manager_id, 1
  FROM employee WHERE manager_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.manager_id, a.level + 1
  FROM employee c JOIN tree a ON c.manager_id = a.id
)
SELECT * FROM tree ORDER BY level;

MySQL 8.0 (and MariaDB 10.2+) support standard-compliant WITH RECURSIVE syntax. Recursion depth is limited by the cte_max_recursion_depth system variable (default 1000). Older versions required writing a loop in a stored procedure.

SQL Server

WITH tree (id, name, manager_id, level) AS (
  SELECT id, name, manager_id, 1
  FROM employee WHERE manager_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.manager_id, a.level + 1
  FROM employee c JOIN tree a ON c.manager_id = a.id
)
SELECT * FROM tree ORDER BY level
OPTION (MAXRECURSION 100);

In SQL Server the RECURSIVE keyword is not written for a recursive CTE; a CTE containing UNION ALL that references itself is treated as recursive automatically. The default recursion limit is 100; change it with OPTION (MAXRECURSION n) (0 = unlimited, use with care).

MongoDB

// Closest equivalent: the $graphLookup stage
db.employee.aggregate([
  { $match: { manager_id: null } },
  { $graphLookup: {
      from: "employee",
      startWith: "$id",
      connectFromField: "id",
      connectToField: "manager_id",
      as: "subordinates"
  } }
])

MongoDB has no recursive CTE; the $graphLookup aggregation stage is used for hierarchical traversal. connectFromField/connectToField set the link fields and maxDepth sets the depth limit. It resolves tree and graph relationships in a single query.

Redis

# Not applicable: Redis has no recursive queries.

Redis offers no query language. Hierarchy/graph traversal is done in application code or server-side with a Lua script; in core Redis the tree walk is written on the client side. (Support for the formerly used RedisGraph module ended in 2023.)

Elasticsearch

# Not applicable: recursive queries are not supported.
# Approach: model with parent-child (join field) or nested,
# deep hierarchies are traversed on the application side.

Elasticsearch offers no recursive query (no equivalent of WITH RECURSIVE / CONNECT BY). Hierarchical relationships are modeled with the 'join' (parent/child) field type or by storing the tree path (materialized path) in a field; multi-level traversal is usually done in the application.