19:59

	Content	Login

SQL Problems and solutions S. I. Moiseenko

Content

SQL console

back go forward

New sql features

Common table expressions (CTE)

Recursive CTE page 1

A CTE (common table expression) allows to determine a table in framework of a query for multiple referencings.CTE has one more important. With it, you can write a recursive query, i.e. request, which, once written, will be repeated many times until some condition is true. A recursive CTE is as follows:

WITH < name >[(< COLUMN list >)]
AS(
< SELECT... > -- Anchor part.
UNION ALL -- Recursive part.
< SELECT...FROM < name >… > 
WHERE < condition FOR the continuation of iterations >
)

A recursive CTE-query differs from ordinary CTE in recursive part only that introduced by UNION ALL statement. Note that a there is reference to CTE name in recursive part, i.e., CTE refers to itself within itself. This is, in fact, there is recursion. Naturally, anchor and recursive parts must have identical column set.

We turn to the example. Consider the task of getting the alphabet, i.e. table of alphabetic characters - uppercase Latin letters. To be a base for comparison, we solve this problem, first by generating a numerical sequence, which was seen in section 8.1.

Console

Execute

SELECT CHAR(ASCII('A')+5*(a-1) + b-1) AS num 
FROM (SELECT 1 a UNION ALL SELECT 2 UNION ALL SELECT 3 
 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
 ) x CROSS JOIN
 (SELECT 1 b UNION ALL SELECT 2 UNION ALL SELECT 3 
 UNION ALL SELECT 4 UNION ALL SELECT 5 
 ) y 
WHERE 5*(a-1) + b <= 26
ORDER BY 1;

The decision via the recursive CTE:

Console

Execute

WITH Letters AS(
SELECT ASCII('A') code, CHAR(ASCII('A')) letter
UNION ALL
SELECT code+1, CHAR(code+1) FROM Letters
WHERE code+1 <= ASCII('Z')
)
SELECT letter FROM Letters;

In anchor part of query we define ASCII-code of the first letters of the alphabet and the corresponding symbol. In recursive part of query we simply increase the ASCII-code by one, referring to the CTE in the FROM clause. As a result, the lines with next letters will be added sequentially (UNION ALL) after line with first letter, in the order of their ASCII-code. Iteration will continue as long as the condition code +1 <= ascii ('Z') is true, i.e. until a letter Z will not be added.

The statement

Console

Execute

SELECT letter FROM Letters

actually intended to refer to the CTE, start recursion and output results. Everything else can be regarded as a definition. Note that by default is allowed 100 iterations. This means that if the termination condition is not fulfilled earlier iterations, then the recursion is stopped after 100 iterations. Maximum number of iterations can be changed using "hint"

OPTION(MAXRECURSION N)

where N - the maximum number of iterations. A value of 0 does not limit the number of iterations. We must carefully use this value as is fraught with cycling. If the query was not completed within a specified number of iterations, an error (the rows received to this time are returned):

The statement terminated. The maximum recursion N has been exhausted before statement completion.

Pages