SQL Pocket Guide, 3rd Edition

Hierarchical Queries

DB2, Oracle, and SQL Server support the recursive use of WITH as defined in the ISO SQL standard for querying hierarchical and recursive data. PostgreSQL supports recursive WITH, but with a slight syntax difference.

Note

Oracle also supports a proprietary CONNECT BY syntax. See CONNECT BY Queries.

Recursive WITH

Following is an example recursive query that generates a hierarchical list of governmental units. States will be listed first, then counties, then townships.

WITH recursiveGov
   (depth, 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.depth+1, child.id,
           child.parent_id, child.name,
           child.type
    FROM recursiveGOV parent, gov_unit child
    WHERE child.parent_id = parent.id)
SELECT depth, id, parent_id, name, type
FROM recursiveGOV;

PostgreSQL requires that you specify that the WITH clause is to be recursive by including the RECURSIVE keyword:

WITH RECURSIVE recursiveGov
...

Most of the preceding 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 ...