A recursive CTE

Some data is a tree, such as categories inside categories, and you do not know how deep it goes. A recursive CTE walks a tree of any depth, and this lesson uses the marketplace category tree from learn.shop.

How it works

A recursive CTE has two parts joined by UNION ALL. The second part reads the CTE itself, joins the rows found so far to the table to find the next level, and repeats until a pass returns no rows.

Common mistake

If the recursive join compares the wrong columns, it can match nothing. The query then finishes with no error and too few rows, so a tree that looks too shallow is a sign to check the join.

Worked example

WITH RECURSIVE numbers AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM numbers WHERE n < 5
)
SELECT n FROM numbers ORDER BY n

The first part gives the single row 1. Each pass adds one to the last row and stops when n reaches 5.

A category tree works the same way, except each pass joins to the table to find the children.

All BigQuery lessons