This article will introduce the concept of SQL recursive. Recursive CTE is a really cool. We will see that it can often simplify our code, and avoid a cascade of SQL queries!
The recursive queries are used to query hierarchical data. It avoids a cascade of SQL queries, you can only do one query to retrieve the hierarchical data.
First, what is a CTE? A CTE (Common Table Expression) is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. For example, you can use CTE when, in a query, you will use the same subquery more than once.
A recursive CTE is one having a subquery that refers to its own name!
Recursive CTE is defined in the SQL standard.
A recursive CTE has this structure:
In this example, we use hierarchical data. Each row can have zero or one parent. And it parent can also have a parent etc.
Create table test (id integer, parent_id integer);
insert into test (id, parent_id) values (1, null);
insert into test (id, parent_id) values (11, 1);
insert into test (id, parent_id) values (111, 11);
insert into test (id, parent_id) values (112, 11);
insert into test (id, parent_id) values (12, 1);
insert into test (id, parent_id) values (121, 12);
For example, the row with id 111 has as ancestors: 11 and 1.
Before knowing the recursive CTE, I was doing several queries to get all the ancestors of a row.
For example, to retrieve all the ancestors of the row with id 111.
While (has parent)
Select id, parent_id from test where id = X
With recursive CTE, we can retrieve all ancestors of a row with only one SQL query :)
WITH RECURSIVE cte_test AS (
SELECT id, parent_id FROM test WHERE id = 111
SELECT test.id, test.parent_id FROM test JOIN cte_test ON cte_test.id = test.parent_id
) SELECT * FROM cte_test
It indicates we will make recursive
It is the initial query.
It is the recursive expression! We make a jointure with the current CTE!
Replay this example here
More information here
Create your free account to unlock your custom reading experience.