Last modified on 2 March 2010, at 17:32

SQL Dialects Reference/Select queries/Hierarchical Queries

Hierarchical QueriesEdit

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