RECURSIVE in SQL

Question

I am learning SQL and have difficulty understanding the following recursive SQL statement.

WITH RECURSIVE t(n) AS ( SELECT 1 UNION ALL SELECT n+1 FROM t WHERE n < 100 ) SELECT sum(n) FROM t; 

What is n and t from SELECT sum(n) FROM t; ? As I understand it, n is the number t - the set. I'm right?

Also, how is recursion called in this statement?

Answer 1

The syntax you use looks like Postgres. The "recursion" in SQL is not really a recursion, it is an iteration. Your expression:

 WITH RECURSIVE t(n) AS ( SELECT 1 UNION ALL SELECT n+1 FROM t WHERE n < 100 ) SELECT sum(n) FROM t; 

The statement for t is evaluated as:

• Rate the non-self-linking part ( select 1 ).
• Then evaluate the self-splicing part. (This initially gives 2.)
• Then re-evaluate the self-imposing part. (3).
• And so on, while the condition is still valid ( n < 100 ).

When this is done, the subquery t will be completed, and the final statement can be evaluated.

Answer 2

This is called the Common Table Expression or CTE.

RECURSIVE doesn't mean anything from the query: it's just another name, like n or t . What makes things recursive is that a CTE named t refers to itself inside an expression. To get the result of an expression, the query mechanism must therefore recursively build the result, where each estimate starts the next. He reaches this point: SELECT n+1 FROM t... and must stop and evaluate t . To do this, he must call himself again and so on until the condition ( n < 100 ) is no longer fulfilled. SELECT 1 provides the starting point, and WHERE n < 100 ensures that the query is not returned forever.

At least that’s how it should work conceptually. What actually happens is that the query mechanism constructs the result iteratively, not recursively, if possible, but this is another story.

Answer 3

Separate it separately:

 WITH RECURSIVE t(n) AS ( 

A common table expression (CTE), which should include an initial query and a recursive query. CTE is called t and returns 1 column: n

Serial request:

  SELECT 1 

returns ans answer set (in this case only one line: 1) and places a copy of it in the final answer set

Now the recursive part begins:

  UNION ALL 

The rows returned from the seed query are now processed, and n + 1 is returned (again a set of answers from one row: 2) and is copied to the final set of answers:

  SELECT n+1 FROM t WHERE n < 100 

If this step returns a nonempty set of responses (activity_count> 0), it repeats (forever).

The WHERE clause for a computation such as n + 1 is usually used to avoid infinite recursion. Usually, as a rule, the maximum possible level based on data is known, and for complex queries it is too easy to establish some conditions, -)

Finally, a set of answers is returned:

 ) SELECT sum(n) FROM t; 

When you just do SELECT * FROM t; , you will see all numbers from 1 to 100, this is not a very effective way to create this list.

The most important thing to remember is that each step creates a part of the final result, and only the next lines of the previous step are processed at the next recursion level.