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.
- Write
WITH RECURSIVE name AS (first query UNION ALL recursive query) SELECT ... FROM name. - The recursive query reads the CTE by its own name. Join it to the table on the child side, for example
c.parent_id = cd.id. - Carry a counter such as depth + 1 to record how many steps each row is from the start.
- The recursion ends when a pass adds no rows. If it never does, for example because a
parent_idloops back on itself, BigQuery stops the query with an error after 500 iterations.
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 nThe 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.