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, MSSQLMS SQL does not allow the RECURSIVE keyword PostgreSQL SQLite 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