SQLite 3.27

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE Tree
       (parent INTEGER,
        child  INTEGER);
✓
INSERT INTO Tree
       VALUES -- (parent -> child)
              (1, 2), (1, 3), (1, 4),
              (2, 5), (2, 11), (3, 9),
              (5, 6), (5, 7),  (5, 8),
              (9, 10), (11, 12);
✓
WITH anchor AS (
SELECT parent, child
FROM tree
WHERE parent = 2
LIMIT 1
), children_i(parent,child) AS (
-- anchor/initial values
SELECT parent, child
FROM anchor
UNION ALL
-- recursion
SELECT c1.child, t1.child
FROM tree t1
JOIN children_i c1
  ON t1.parent = c1.child)
SELECT *  FROM children_i;


parent
child


2
5

5
6

5
7

5
8