SQL Pocket Guide, 2nd Edition

Hierarchical Queries

Direct support for querying hierarchical data falls into two camps: SQL Server and DB2. Both support the use of the ISO SQL standard’s recursive WITH clause. Oracle implements non-standard CONNECT BY syntax.

Note

MySQL does not implement specific syntax in support of recursive, hierarchical queries. In PostgreSQL, support for WITH is planned for the 8.2 release.

Recursive WITH (SQL Server, DB2)

SQL Server and DB2 support the recursive use of WITH as defined in the ISO SQL standard for querying hierarchical and recursive data. For example:

WITH recursiveGov
   (level, id, parent_id, name,
    type) AS
   (SELECT 1, parent.id, parent.parent_id,
           parent.name, parent.type
    FROM gov_unit parent
    WHERE parent.parent_id IS NULL
    UNION ALL
    SELECT parent.level+1, child.id,
           child.parent_id, child.name,
           child.type
    FROM recursiveGOV parent, gov_unit child
    WHERE child.parent_id = parent.id)
SELECT level, id, parent_id, name, type
FROM recursiveGOV;

Most of this statement consists of a subquery named recursiveGOV that is specified using the WITH clause. The subquery consists of two SELECTs unioned together. Consider the first SELECT as the union query’s starting point. It includes a predicate to treat rows having null parent_ids as the tree roots. Consider the second SELECT as defining the recursive link between parent and child rows.

The second SELECT brings in the children of the first. Because the second SELECT references the named subquery that it is part of (itself), it recursively brings ...