» Documentation » SQL Reference

Recursive queries (CTE)

Recursive queries

Recursive Queries syntax is very similiar to SQL Server (using Common Table Expressions). There are many articles about it.

For example, create a table and populate with some rows:

Hierarchical relations can be expressed as a recursive single tables' join. For example, consider table:

create table objects
(
id int not null primary key partition,
name varchar,
parent_id int
); 
go;
-- insert hierarchical data
insert into objects(id,name,parent_id) values(1,'Cars',null);
  insert into objects(id,name,parent_id) values(2,'Porsche',1);
    insert into objects(id,name,parent_id) values(3,'911',2);
    insert into objects(id,name,parent_id) values(4,'Boxster',2);
    insert into objects(id,name,parent_id) values(5,'Cayman',2);
    insert into objects(id,name,parent_id) values(6,'Cayenne',2);
    insert into objects(id,name,parent_id) values(7,'Panamera',2);
  insert into objects(id,name,parent_id) values(8,'Aston Martin',1);
    insert into objects(id,name,parent_id) values(9,'DB7',8);
    insert into objects(id,name,parent_id) values(10,'DB9',8);
    insert into objects(id,name,parent_id) values(11,'Vantage',8);
    insert into objects(id,name,parent_id) values(12,'One',8); 

Here we have created hierarchy using parent_id field that refers to the parent row: "Cars" is the root (no parent), 2 brands (Porsche and Aston Martin) belongs to "Cars" and each brand has a list of models. Now, define the recursive CTE SQL to read all porsche models:

WITH Cars(id,parent_id,name,path,level) AS 
( 
   --initialization. read porsche root
   SELECT id,parent_id,name,name,0 as level
   FROM objects
   WHERE id = 2 -- porsche root
   UNION ALL 
   --recursive execution 
   SELECT o.id,o.parent_id,o.name,concat(c.path,'/', o.name), c.level+1
   FROM cars c
      INNER JOIN objects o ON c.id = o.parent_id 
) 
select * from cars; 

The result:

Id

Parent_Id

Name

Path

Level

2

1

Porsche

Porsche

0

3

2

911

Porsche/911

1

4

2

Boxster

Porsche/Boxster

1

5

2

Cayman

Porsche/Cayman

1

6

2

Cayenne

Porsche/Cayenne

1

7

2

Panamera

Porsche/Panamera

1

Example using parameterized query and ordering:

Declare @id int
set @id = select id from objects where name = 'porsche'; 

WITH Cars(id,parent_id,name,path,level) AS 
( 
   --initialization. read porsche root
   SELECT id,parent_id,name,name,0 as level
   FROM objects
   WHERE id = @id
   UNION ALL 
   --recursive execution 
   SELECT o.id,o.parent_id,o.name,concat(c.path,'/', o.name), c.level+1
   FROM cars c
      INNER JOIN objects o ON c.id = o.parent_id 
) 
select * from cars order by name;