Hierarchical Queries []

Moved to the page http://3rdstage.wikia.com/wiki/Simple_Patterns_and_Typical_Samples#Hierarchical_Queries in my wiki as of 15th Jan. 2014.

There are two types of hierarchical queries handling tree structure data in adjacency list model. (table has recursive relationship using foreign key on parent_id column)
One is (START WITH) CONNECT BY clause provided by Oracle and the other is recursive WITH clause(recursive common table expressions).
Although CONNECT BY clause is much more intuitive, robust and simple, WITH clause is ANSI-SQL standard syntax.

CONNECT BY clause

• Supported by : Oracle 9i +, DB2 9.7 (the latest version)
• Hierarchical Queries of Oracle Database 10g

SELECT ~ 
FROM ~ 
WHERE ~
START WITH parent_id IS NULL
CONNECT BY PRIOR id = parent_id

Recursive WITH clause

• Supported by : DB2, SQL Server 2005+, PostgreSQL 8.4 (the latest version)
• ANSI-SQL syntax
• http://www.ibm.com/developerworks/data/library/techarticle/dm-0510rielau/
• http://msdn.microsoft.com/en-us/library/ms175972.aspx
• http://www.postgresql.org/docs/8.4/interactive/queries-with.html

Examples by Database

The structure of the table for the example statements below is defined by the following DDL.

create table category(
 id varchar2(10) not null,
 name varchar2(60) not null,
 parent_id varchar2(10),
 seq number(5, 0),
 descn varchar2(2000),
 constraint pk_category primary key (id),
 constraint fk_cateogry_1 foreign key (parent_id) references category(id)
);

Oracle 9i or higher

The basic query is :

select id, name, parent_id, seq, descn, 
from category
start with parent_id is null
connect by prior id = parent_id

Oracle 9i provides LEVEL pseudo-column, SYS_CONNECT_BY function and ORDER SIBLINGS BY clause which can be used in hierarchical queries.

select id, name, parent_id, seq, level, descn, 
       sys_connect_by_path(id, '//') as id_path,
       sys_connect_by_path(name, '//') as name_path
from category
start with parent_id is null
connect by prior id = parent_id
order siblings by seq, name

Oracle 10g provides additional CONNECT_BY_ISLEAF pseudo-column, CONNECT_BY_ISCYCLE pseudo-column and CONNECT BY NOCYCLE.

select id, name, parent_id, seq, level, descn, 
       sys_connect_by_path(id, '//') as id_path,
       sys_connect_by_path(name, '//') as name_path,
       connect_by_isleaf as is_leaf
from category
start with parent_id is null
connect by nocycle prior id = parent_id and level >= 5
order siblings by seq, name

Microsoft SQL Server 2005 or higher

The basic hierarchical query is

with v_category("id", "name", "level", parent_id) as (
   select "id", "name", 1 as "level", parent_id
   from category 
   where id is null
   union all
   select a."id", b."level" + 1, a.parent_id
   from category as a inner join v_category as b on a.parent_id = b."id"
)
select "id", "name", "level", parent_id
from v_category

Readings

• Adjacency list model
• Subquery factoring in Oracle 9i
• Recursive Subquery Factoring Examples of Oracle 11.2
  Oracle at last added recursive feature to existing with clause.
• DB2 9.7: Run Oracle applications on DB2 9.7 for Linux, Unix, and Windows

misc

• MySQL (up to 6.0, latest version) still doesn't support hierarchical query.