A recursive CTE needs an anchor, a recursive term, and a termination — org charts, paths.

The anchor answers 'where does the walk start'; the recursive member answers 'given everything found so far, what is one more step'; the engine unions each step's NEW rows until a step is empty. Depth is just a column you carry (+1 per step). Two safety facts: termination is a property of the data (acyclic) plus your predicates — a cycle loops, and a depth guard (WHERE d < 20) is cheap insurance; and the step joins the CTE's previous rows, not the whole accumulation, which is why it walks levels, not everything at once.

SELECT
      key_id,
      opens_lockFROM
      keys
    WHERE
  sub_of = 'GRAND'
    ORDER BY
  key_id;

WITH RECURSIVE
  walk (key_id, opens_lock, depth) AS (
    SELECT
      key_id,
      opens_lock,
      0
    FROM
      keys
    WHERE
      key_id = 'GRAND'
    UNION ALL
    SELECT
      k.key_id,
      k.opens_lock,
      w.depth + 1
    FROM
      keys k
      JOIN walk w ON k.sub_of = w.key_id
    WHERE
      w.depth < 20
  )
SELECT
  key_id,
  opens_lock,
  depth
FROM
  walk
ORDER BY
  depth,
  key_id;

The one-level query found only GRAND's direct keys and missed the sub-keys beneath them — a recursive CTE walks the whole hierarchy, one level per step, carrying a depth.

WITH RECURSIVE plus a depth column and a guard; the RECURSIVE keyword is optional in some engines (BigQuery keeps it, SQL Server omits it).

The RECURSIVE keyword is required in DuckDB and Postgres, optional or absent in some engines.