Using Recursive CTE to Compute Factorial in SQL

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 :

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

2. Key Components :

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

3. Why use Recursive CTEs? :

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

4. Real-World Application :

1. 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.

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

Previous SQL Exercise:Use Window Functions.
Next SQL Exercise: Pivot Data Using PIVOT Operator.