SQL Dialects Reference/Select queries/Hierarchical Queries

Hierarchical Queries

Hierarchical queries are a way to extract information from a table that is linked with itself.

Let's say we have the following table:

My example table: 

id     of type numeric 
father of type numeric, that references an id of other register of the same table 
data   rest of fields, etc

If we have the following values:

id     father        data 
50     null          The boss
51     50            The well positioned manager
52     50            Another well positioned manager 
53     51            The worker
54     52            Another worker
5      null          Other node 
10     5             The son of Other node

The values that "hang" from node 50 are the values 50, 51, 52, 53, 54 but not 5 nor 10.

• DB2

or

• Firebird / InterBase

• Ingres, MySQL, MSSQL^[1]

• PostgreSQL

WITH RECURSIVE t AS (
    SELECT id, father FROM "table" WHERE id = 50 AND father IS NULL
UNION ALL
    SELECT t1.id, t1.father FROM t JOIN "table" t1 ON (t1.father = t.id)
)
SELECT * FROM t;

• Oracle, Linter

SELECT * FROM table CONNECT BY id = PRIOR father START WITH id = 50

1. ↑ MS SQL does not allow the RECURSIVE keyword