Using Recursive CTE to Compute Factorial in SQL

Last update on September 25 2025 12:33:43 (UTC/GMT +8 hours)

Create a Recursive Common Table Expression (CTE)

Write a SQL query to calculate the factorial of a number using a recursive CTE.

Solution:

-- Calculate the factorial of a number using a recursive CTE.
WITH FactorialCTE AS (
    SELECT 1 AS Number, 1 AS Factorial -- Base case.
    UNION ALL
    SELECT Number + 1, Factorial * (Number + 1)
    FROM FactorialCTE
    WHERE Number < 10 -- Limit recursion depth.
)
SELECT * FROM FactorialCTE;

Explanation:

1. Purpose of the Query :

The goal is to demonstrate how to use a recursive CTE to calculate the factorial of numbers up to 10.

2. Key Components :

WITH FactorialCTE : Defines the recursive CTE.
Base Case : Initializes the recursion.
Recursive Step : Calculates the next factorial value.
UNION ALL : Combines base and recursive results.

3. Why use Recursive CTEs? :

Recursive CTEs are ideal for hierarchical or iterative calculations that cannot be expressed with simple queries.

4. Real-World Application :

For example, in organizational databases, you might use recursive CTEs to traverse employee hierarchies (e.g., manager-subordinate relationships).

Additional Notes:

Be cautious with recursion depth to avoid infinite loops or performance issues.
Use this exercise to teach how to handle hierarchical data structures.

For more Practice: Solve these Related Problems:

Write a SQL query to calculate the Fibonacci sequence up to the 10th term using a recursive CTE.
Write a SQL query to compute the sum of the first 20 natural numbers using a recursive CTE.
Write a SQL query to generate a multiplication table for numbers 1 through 10 using a recursive CTE.
Write a SQL query to find all ancestors of a given employee in an organizational hierarchy using a recursive CTE.

Go to:

PREV : Use Window Functions.
NEXT : Pivot Data Using PIVOT Operator.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.