SQL Pocket Guide, 3rd Edition

CONNECT BY Queries

Oracle Database supports CONNECT BY syntax for executing hierarchical queries. Beginning in Oracle Database 11g Release 2, you should consider the WITH clause, which in that release supports ISO standard syntax for recursive queries. See Hierarchical Queries.

Note

DB2 optionally supports CONNECT BY for compatibility with Oracle. There are some limitations, and support needs to be enabled through B2_COMPATIBILITY_VECTOR.

Core CONNECT BY Syntax

To return data in a hierarchy, specify a starting node using START WITH, and specify the parent-child relationship using CONNECT BY:

SELECT id, name, type, parent_id
FROM gov_unit
START WITH parent_id IS NULL
CONNECT BY parent_id = PRIOR id;

ID    NAME       TYPE     PARENT_ID
----- ---------- -------- ---------
3     Michigan   state
2     Alger      county   3
1     Munising   city     2
4     Munising   township 2
5     Au Train   township 2
6     Baraga     county   3
7     Ontonagon  county   3
8     Interior   township 7
9     Dickinson  county   3
10    Gogebic    county   3
11    Delta      county   3
12    Masonville township 11
...

The START WITH clause identifies the row(s) Oracle considers to be at the top of the tree(s). There is only one tree in this example, and it is for the state of Michigan. Alger County is a subdivision of Michigan. Munising and Au Train Townships are subdivisions of Alger County. Each entity’s parent_id points to its enclosing entity.

Your START WITH condition does not necessarily need to involve the columns that link parent to child nodes. For example, use the following to generate a tree for ...