Recursive CTEs

A recursive CTE is a query that references itself, looping through parent-child relationships one level at a time until it reaches the end of the chain.

Try this lesson free →

About 5–10 minutes · no sign-up needed

Example

Walking Retail Product Hierarchies with Recursive CTEs

WITH RECURSIVE product_tree AS (SELECT product_id, product_name, parent_product_id, 0 AS depth FROM products WHERE parent_product_id IS NULL UNION ALL SELECT p.product_id, p.product_name, p.parent_product_id, pt.depth + 1 FROM products p JOIN product_tree pt ON p.parent_product_id = pt.product_id) SELECT product_id, product_name, depth FROM product_tree ORDER BY depth, product_id

Result:

product_id	product_name	depth
1	Electronics	0
5	Clothing	0
2	Smartphones	1
3	iPhone 15 Pro	2
4	Samsung Galaxy S24	2

Step by step

WITH lets you name a sub-query so the main query can reference it.

WITH RECURSIVE product_tree AS (SELECT product_id, product_name, parent_product_id, 0 AS depth FROM products WHERE parent_product_id IS NULL UNION ALL SELECT p.product_id, p.product_name, p.parent_product_id, pt.depth + 1 FROM products p JOIN product_tree pt ON p.parent_product_id = pt.product_id)

After SELECT, list the column names you want, separated by commas.

WITH RECURSIVE product_tree AS (SELECT product_id, product_name, parent_product_id, 0 AS depth FROM products WHERE parent_product_id IS NULL UNION ALL SELECT p.product_id, p.product_name, p.parent_product_id, pt.depth + 1 FROM products p JOIN product_tree pt ON p.parent_product_id = pt.product_id)
SELECT product_id, product_name, depth

FROM tells SQL which table to pull the data from.

WITH RECURSIVE product_tree AS (SELECT product_id, product_name, parent_product_id, 0 AS depth FROM products WHERE parent_product_id IS NULL UNION ALL SELECT p.product_id, p.product_name, p.parent_product_id, pt.depth + 1 FROM products p JOIN product_tree pt ON p.parent_product_id = pt.product_id)
SELECT product_id, product_name, depth
FROM product_tree

Practice

Try each one in your head first, then open the answer.

1. Write a recursive CTE that generates the numbers 1 through 5.

WITH RECURSIVE nums AS (SELECT 1 AS n UNION ALL SELECT n + 1 FROM nums WHERE n < 5) SELECT * FROM nums

2. Write a recursive CTE that generates the numbers 1 through 5.

WITH RECURSIVE nums AS (SELECT 1 AS n UNION ALL SELECT n + 1 FROM nums WHERE n < 5) SELECT * FROM nums

3. Write a recursive CTE that generates the numbers 1 through 5.

WITH RECURSIVE nums AS (SELECT 1 AS n UNION ALL SELECT n + 1 FROM nums WHERE n < 5) SELECT * FROM nums

4. Write a recursive CTE that generates the numbers 1 through 5.

WITH RECURSIVE nums AS (SELECT 1 AS n UNION ALL SELECT n + 1 FROM nums WHERE n < 5) SELECT * FROM nums

5. Write a recursive CTE that generates the numbers 1 through 5.

WITH RECURSIVE nums AS (SELECT 1 AS n UNION ALL SELECT n + 1 FROM nums WHERE n < 5) SELECT * FROM nums

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →

About 5–10 minutes · no sign-up needed