DEV Community

Cover image for Mastering SQL's Recursive CTEs
Anil Kaundal
Anil Kaundal

Posted on

Mastering SQL's Recursive CTEs

Common Table Expressions (CTEs) are a powerful feature in SQL, and when combined with recursion, they become even more formidable. Recursive CTEs allow you to generate sequences, hierarchies, and perform other complex operations with elegance. In this blog, we'll explore the basics and provide practical examples to demonstrate their potential.

Understanding Recursive CTEs

A Recursive CTE is like having a reusable script, where a query refers to its own output. This feature is ideal for tasks that involve iteration or hierarchical data.

Example 1: Generating a Sequence of Numbers

Let's start with a classic example: generating a sequence of numbers. In this case, we want to create a sequence from 1 to 10.

  SELECT n + 1 FROM numbers WHERE n < 10
SELECT * FROM numbers;
Enter fullscreen mode Exit fullscreen mode

Sequence of Numbers

This query initializes the CTE with 1, then repeatedly adds 1 to the previous value until the condition n < 10 is met. The result is a sequence from 1 to 10.

Example 2: Calculating Factorials

Recursive CTEs are not limited to sequences. You can also use them for more complex calculations, like calculating factorials.

WITH RECURSIVE factorials AS (
  SELECT 1 AS n, 1 AS factorial
  SELECT n + 1, (factorial * (n + 1)) FROM factorials WHERE n < 9
SELECT * FROM factorials;
Enter fullscreen mode Exit fullscreen mode

Calculating Factorials

Here, we calculate factorials from 1 to 9. The CTE multiplies the current factorial by the next number in the sequence until n < 9.


Recursive CTEs are a powerful tool in SQL, enabling you to solve various problems efficiently. They are not limited to simple sequences and can handle complex calculations and hierarchies. Understanding how to use them can significantly enhance your SQL skills and make your queries more elegant and concise.

Happy querying! 🚀📊

Top comments (0)