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.
Oracle also supports a proprietary CONNECT BY syntax. See CONNECT BY Queries.
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
The second SELECT brings in the children of the first. Because the second SELECT ...