SQL Dialects Reference/Select queries/Hierarchical Queries
Appearance
Hierarchical Queries
[edit | edit source]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
- 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
- ↑ MS SQL does not allow the RECURSIVE keyword