Hierarchical and recursive queries in SQL

A hierarchical query is a type of SQL query that handles hierarchical model data.

Standard SQL specifies hierarchical queries by way of recursive common table expressions (CTEs). Recursive CTEs are supported by systems including IBM DB2, Microsoft SQL Server, Firebird 2.1 and PostgreSQL 8.4.

An alternative syntax is the non-standard CONNECT BY construct.

CONNECT BY

"CONNECT BY" is supported by EnterpriseDB and Oracle database.. Example query:

SELECT select_list
FROM table_expression
[ WHERE ... ]
[ START WITH start_expression ]
CONNECT BY { PRIOR parent_expr = child_expr | child_expr = PRIOR parent_expr }
[ ORDER SIBLINGS BY column1 [ ASC | DESC ] [, column2 [ ASC | DESC ] ] ...
[ GROUP BY ... ]
[ HAVING ... ]
...

For example

 SELECT LEVEL, LPAD (' ', 2 * (LEVEL - 1)) || ename "employee", empno, mgr
 FROM emp START WITH mgr IS NULL
 CONNECT BY PRIOR empno = mgr;

The output from the above query would look like:

 level |  employee   | empno | mgr
-------+-------------+-------+------
     1 | KING        |  7839 |
     2 |   JONES     |  7566 | 7839
     3 |     SCOTT   |  7788 | 7566
     4 |       ADAMS |  7876 | 7788
     3 |     FORD    |  7902 | 7566
     4 |       SMITH |  7369 | 7902
     2 |   BLAKE     |  7698 | 7839
     3 |     ALLEN   |  7499 | 7698
     3 |     WARD    |  7521 | 7698
     3 |     MARTIN  |  7654 | 7698
     3 |     TURNER  |  7844 | 7698
     3 |     JAMES   |  7900 | 7698
     2 |   CLARK     |  7782 | 7839
     3 |     MILLER  |  7934 | 7782
(14 rows)

Pseudocolumns

• LEVEL
• CONNECT_BY_ISLEAF
• CONNECT_BY_ISCYCLE

unary operators

• CONNECT_BY_ROOT

Functions

• SYS_CONNECT_BY_PATH

See also

• Hierarchical model
• Tree structure