Recursive queries are used to query hierarchical data. The SQL standard defines a special syntax for common table expressions to enable recursive processing.

Assume the following hierarchical definition of product categories:

create table category
(
   id               integer      not null primary key,
   name             varchar(100) not null,
   parent_category  integer references category
);

With the following data:

id | name              | parent_category
---+-------------------+----------------
 1 | Root Node         |          (null)
 2 | Software          |               1
 3 | Hardware          |               1
 4 | Notebooks         |               3
 5 | Phones            |               3
 6 | Applications      |               2
 7 | Database Software |               2
 8 | Relational DBMS   |               7
 9 | Tools             |               7
10 | Commandline tools |               9
11 | GUI Tools         |               9
12 | Android Phones    |               5
13 | iPhone            |               5
14 | Windows Phones    |               5

To (recursively) query all sub-categories for one parent, the following query can be used:

with recursive cat_tree as (
   select id,
          name,
          parent_category
   from category
   where name = 'Database Software'  -- this defines the start of the recursion
   union all
   select child.id,
          child.name,
          child.parent_category
   from category as child
     join cat_tree as parent on parent.id = child.parent_category -- the self join to the CTE builds up the recursion
)
select *
from cat_tree;

The above query will return the following result:

id | name              | parent_category
---+-------------------+----------------
 7 | Database Software |               2
 8 | Relational DBMS   |               7
 9 | Tools             |               7
10 | Commandline tools |               9
11 | GUI Tools         |               9

More information about recursive queries in Wikipedia

Back to the SQL Feature Comparison