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.

or 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; SELECT * FROM table CONNECT BY id = PRIOR father START WITH id = 50
 * DB2
 * Firebird / InterBase
 * Ingres, MySQL, MSSQL
 * PostgreSQL
 * SQLite
 * Oracle, Linter