Recursive SQL queries

Recursive SQL queries are one way to solve a tree problem and other problems that require recursive processing. They were added to the SQL 99 standard. Before that, they already existed in Oracle. Despite the fact that the standard was released so long ago, the implementation was late. For example, in MS SQL they appeared only in the 2005th server.

Recursive queries are used quite rarely, primarily because of their complicated and incomprehensible syntax:

with [ recursive ] <query_alias_name> [(<column list>)]
as (<request>)
<main request>

In MS SQL there is no recursive keyword, and otherwise everything is the same. This syntax is supported in DB2, Sybase iAnywhere, MS SQL and in all databases that support the SQL 99 standard.

Easier to disassemble the example. Suppose there is a table:

create table tree_sample (
id integer not null primary key ,
id_parent integer foreign key references tree_sample (id),
nm varchar (31))

id - id
id_parent - link to parent
nm - the name.
')
To display the tree:

with recursive tree (nm, id, level , pathstr)
as ( select nm, id, 0, cast ( '' as text)
from tree_sample
where id_parent is null
union all
select tree_sample.nm, tree_sample.id, t. level + 1, tree.pathstr + tree_sample.nm
from tree_sample
inner join tree on tree.id = tree_sample.id_parent)
select id, space ( level ) + nm as nm
from tree
order by pathstr

This example will display a tree with an indented table. The first query from tree_sample This query will return all the roots of the tree. The second query connects the table tree_sample and tree, which is determined by the same query. This query complements the table with tree nodes.

First the first request is executed. Then the results of the second query are added to its results, where the data on the tree table is the result of the first query. Then the second query is executed again, but the data of the tree table is already the result of the previous execution of the second query. And so on. In fact, the database does not work quite right, but the result will be the same as the result of the described algorithm.

After that, the data of this table can be used in the main query as usual.

I want to note that I am not talking about the applicability of this particular example, but only writing it to demonstrate the capabilities of recursive queries. This query will actually work quite slowly due to the order by.