SQL Reference Guide

Common Table Expressions (CTE) and Recursive Queries

Recursive Queries are useful for querying hierarchical data such as tree structured data using a common table expression (CTE). A CTE can be called repeatedly (recursively), fetching a subset of the results each time until it has built an entire result set. A good example is the way a hierarchy, such as a family tree, can be extracted from a database by recursively calling a CTE that gets all children of a specified parent. They are also a useful technique to "flatten" data from multi-row queries.

Example 1: Count Items

This example shows a way to count items:

WITH RECURSIVE counter (n) AS (

SELECT 1 as n

UNION ALL

SELECT n + 1 FROM counter WHERE n < 10

)

SELECT * from counter;

The output from this example would be:

10|

10 record(s) returned

Example 2:

CREATE TABLE testtable (id int, name char(200));

insert into testtable values (1,'First');

insert into testtable values (2, 'Second');

insert into testtable values (3, 'Third');

WITH RECURSIVE fn(id, name, n)

AS (

SELECT id, name, 1 as n from testtable where id = 1

UNION ALL

SELECT testtable.id, testtable.name, fn.n+1 from fn, testtable where testtable.id = fn.n+1

)

SELECT id, name FROM fn;

The output from this example would be:

id| name |

1| First |

2| Second |

3| Third |

Example 3: Employee Reporting "Tree"

This example builds an employee reporting "tree":

WITH RECURSIVE company_tree_list ( boss_id, emp_id, title, dept_id, level ) AS

(

–- Anchor member is defined.

SELECT emp.name, emp.emp_id, emp.title, edh.dep_id, 0 AS level

FROM employee_list AS emp

INNER JOIN emp_dept_history AS edh

ON e.emp_id, = edh.entity_id AND edh.enddate IS NULL

WHERE boss_id IS NULL

UNION ALL

–- Recursive member is defined referencing cte_name.

SELECT emp.boss_id, emp.emp_id, emp.title, edh.dep_id, level + 1

FROM employee_list AS e

INNER JOIN emp_dept_history AS edh

ON emp.emp_id = edh.entity_id AND edh.enddate IS NULL

INNER JOIN company_tree_list AS d

ON emp.boss_id = d.emp_id

)

SELECT name, emp_id, title, dept_id

FROM company_tree_list

INNER JOIN departments AS dept

ON company_tree_list.dept_id = dept.dept_id

WHERE dept.group = 'Sales' OR level = 0;

Example 4: Report a Category's Parent

create table "admin"."people" (

"id" integer,

"name" character(26),

"parent_id" integer

);

insert into "admin"."people" values('1','Root A ',NULL);

insert into "admin"."people" values('2','Root B ',NULL);

insert into "admin"."people" values('3','Child A1 ','1');

insert into "admin"."people" values('4','Child A2 ','1');

insert into "admin"."people" values('5','Child B1 ','2');

insert into "admin"."people" values('6','Child B2 ','2');

insert into "admin"."people" values('7','Grandchild A1a ','3');

insert into "admin"."people" values('8','Grandchild A1b ','3');

WITH RECURSIVE sub_tree (id, name, relative_depth ) AS (

SELECT id, name, 1 AS relative_depth

FROM people

WHERE name = 'Child A1'

UNION ALL

SELECT cat.id, cat.name, st.relative_depth + 1

FROM people cat, sub_tree st

WHERE cat.parent_id = st.id

)

SELECT * FROM sub_tree;

id | name | relative_depth |

3 | Child A1 | 1 |

7 | Grandchild A1a | 2 |

8 | Grandchild A1b | 2 |

3 record(s) returned

Example 5: Flatten Multiple Rows into a Single Field

create table mytest (

f1 varchar (20),

f2 integer);

insert into mytest values('aaa','1');

insert into mytest values('bbb','2');

insert into mytest values('ccccc','3');

insert into mytest values('ddd','4');

insert into mytest values('eeeeeeee','5');

insert into mytest values('f','6');

insert into mytest values('gggg','7');

insert into mytest values('hhh','8');

WITH RECURSIVE myflat ( flatf1, f2) AS

(

SELECT cast(f1 as varchar(8192)), f2

FROM mytest

where f2 = 1

ORDER BY f2

UNION ALL

SELECT flatf1 || f1, mytest.f2

FROM mytest, myflat parent

where parent.f2 + 1 = mytest.f2

)

SELECT top 1 flatf1

FROM myflat order by f2 desc;

aaabbbcccccdddeeeeeeeefgggghhh |

1 record(s) returned