RECURSIVE

PostgreSQL

Example

WITH RECURSIVE numbers AS (
    SELECT 1
    UNION ALL
    SELECT n+1 FROM numbers WHERE n < 10
)
SELECT * FROM numbers;

Output

1
2
3
4
5
6
7
8
9
10

Explanation

The WITH RECURSIVE clause creates a temporary named result set, numbers. The SELECT 1 initializes the first row of the recursive query. The UNION ALL SELECT n+1 FROM numbers WHERE n < 10 clause recursively generates the next row by adding 1 to the previous row value returned by the numbers result set. This continues until the value n reaches 10. The SELECT * FROM numbers statement then retrieves all of the values generated by the recursive query.

SQL Server

Example

WITH RECURSIVE factorial(n, f) AS (
  SELECT 0, 1  -- Initial condition: 0! = 1
  UNION ALL
  SELECT n+1, (n+1)*f FROM factorial  -- Recursive part
  WHERE n < 5)  -- Stop condition
SELECT * FROM factorial;

Output

 n |  f
---|------
 0 |   1
 1 |   1
 2 |   2
 3 |   6
 4 |  24
 5 | 120

Explanation

In the example, a recursive common table expression (CTE) named factorial is used. Starting with n=0 and f=1 as the initial condition, the SELECT statement generates factorials for n from 1 through 5. The recursion stops when n is more than 5. The final SELECT * FROM factorial; statement returns the results of the recursive calculation.

Oracle

Example

WITH RECURSIVE number_list (number) AS (
    SELECT 1
    FROM dual
    UNION ALL
    SELECT number + 1
    FROM number_list
    WHERE number < 5
)
SELECT * FROM number_list;

Output

NUMBER
------
1
2
3
4
5

Explanation

The SQL statement with the WITH keyword is a stored procedure that Oracle calls a subquery factoring clause, or more generally, a “Common Table Expression” (CTE). The RECURSIVE keyword is used to specify that the query is to be run recursively. The example demonstrates a simple recursion where numbers from 1 to 5 are generated. The recursion starts at 1 (SELECT 1 FROM dual) and continues to add 1 to the previous number (SELECT number + 1 FROM number_list) until it reaches 5 (WHERE number < 5). The resulting set of numbers is then selected for output.

SQLite

Example

WITH RECURSIVE
  cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE x<5)
SELECT x FROM cnt;

Output

1
2
3
4
5

Explanation

In the example, a recursive common table expression cnt(x) is created. This CTE starts with a value of 1 (VALUES(1)) and adds 1 to x until x<5, generating a sequence of numbers from 1 to 5. This sequence is then selected and displayed.