CTE Rekursif: data berbentuk pohon

Recursive CTEs: Tree-shaped Data

Now we get to the real superpower of CTEs. There are situations where data has endlessly nested parent relationships (the classic e-commerce categories arranged as MacBook > Computer > Electronics > Products). Plain SQL struggles to walk these hierarchies without loops.

A Recursive CTE solves this by calling itself, pulling out parent/child levels until it "exhausts" the dependency stack.

How it's built

You need the WITH RECURSIVE keyword, and it splits into 2 required parts separated by UNION ALL:

1. Base case (the query that extracts the root parents or the starting element).
2. Recursive step (the query that joins onto itself to pull in the children).

WITH RECURSIVE branch AS (
  -- Base case: find the top-level category that has no parent (parent_id IS NULL)
  SELECT id, name, parent_id, 1 AS depth
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL

  -- Recursion: pulls in the children that point to parents found in the previous step
  SELECT c.id, c.name, c.parent_id, b.depth + 1
  FROM categories c
  JOIN branch b ON c.parent_id = b.id
)
SELECT * FROM branch ORDER BY depth;