Recursive Common Table Expressions

recursive_ctes.sql

-- count till n
WITH RECURSIVE count_cte AS (
    SELECT 1 AS n -- anchor member
    UNION
    SELECT n + 1 -- recursive member
    FROM count_cte WHERE n < 10
) SELECT * FROM count_cte;

-- factorial
WITH RECURSIVE fact_cte AS (
    SELECT 1 AS n, 1 AS fact
    UNION
    SELECT n + 1, (n + 1) * fact
    FROM fact_cte WHERE n < 10
) SELECT * FROM fact_cte;

-- fibonacci
WITH RECURSIVE fib_cte AS (
    SELECT 1 AS n, 0 AS f1, 1 AS f2
    UNION
    SELECT n + 1, f2 AS f1, f1 + f2
    FROM fib_cte WHERE n < 10
) SELECT * FROM fib_cte;

-- multiplication
WITH RECURSIVE mul_cte AS (
    SELECT 5 AS a, 1 AS b, 5 AS c
    UNION
    SELECT a, b + 1, a * (b + 1)
    FROM mul_cte WHERE b < 10
) SELECT * FROM mul_cte;