Hierarchical Queries

View All Scripts Login to Run Script

Script Name Hierarchical Queries
Description Using connect by and connect by prior to perform hierarchical queries using a self referencing foreign key.
Area SQL General
Contributor Mike Hichwa (Oracle)
Created Monday October 05, 2015

Statement 1

Query with a CONNECT BY clause defines a hierarchical relationship in which the employee_id value of the parent row is equal to the manager_id value of the child row.

SELECT last_name, employee_id, manager_id  
FROM hr.employees 
CONNECT BY employee_id = manager_id 
ORDER BY last_name

 
LAST_NAME EMPLOYEE_ID MANAGER_ID
Abel 174 149
Ande 166 147
Atkinson 130 121
Austin 105 103
Baer 204 101
Baida 116 114
Banda 167 147
Bates 172 148
Bell 192 123
Bernstein 151 145
Bissot 129 121
Bloom 169 148
Bull 185 121
Cabrio 187 121
Cambrault 148 100
Cambrault 154 145
Chen 110 108
Chung 188 122
Colmenares 119 114
Davies 142 124
De Haan 102 100
Dellinger 186 121
Dilly 189 122
Doran 160 146
Ernst 104 103
Errazuriz 147 100
Everett 193 123
Faviet 109 108
Fay 202 201
Feeney 197 124
Fleaur 181 120
Fox 170 148
Fripp 121 100
Gates 190 122
Gee 135 122
Geoni 183 120
Gietz 206 205
Grant 199 124
Grant 178 149
Greenberg 108 101
Greene 163 147
Hall 152 145
Hartstein 201 100
Higgins 205 101
Himuro 118 114
Hunold 103 102
Hutton 175 149
Johnson 179 149
Jones 195 123
Kaufling 122 100
Khoo 115 114

Rows 1 - 50

LAST_NAME	EMPLOYEE_ID	MANAGER_ID
Abel	174	149
Ande	166	147
Atkinson	130	121
Austin	105	103
Baer	204	101
Baida	116	114
Banda	167	147
Bates	172	148
Bell	192	123
Bernstein	151	145
Bissot	129	121
Bloom	169	148
Bull	185	121
Cabrio	187	121
Cambrault	148	100
Cambrault	154	145
Chen	110	108
Chung	188	122
Colmenares	119	114
Davies	142	124
De Haan	102	100
Dellinger	186	121
Dilly	189	122
Doran	160	146
Ernst	104	103
Errazuriz	147	100
Everett	193	123
Faviet	109	108
Fay	202	201
Feeney	197	124
Fleaur	181	120
Fox	170	148
Fripp	121	100
Gates	190	122
Gee	135	122
Geoni	183	120
Gietz	206	205
Grant	199	124
Grant	178	149
Greenberg	108	101
Greene	163	147
Hall	152	145
Hartstein	201	100
Higgins	205	101
Himuro	118	114
Hunold	103	102
Hutton	175	149
Johnson	179	149
Jones	195	123
Kaufling	122	100
Khoo	115	114

Statement 2

Example of a CONNECT BY clause, the PRIOR operator applies only to the employee_id value. To evaluate this condition, the database evaluates employee_id values for the parent row and manager_id, salary, and commission_pct values for the child row

SELECT last_name, employee_id, manager_id  
FROM HR.employees 
CONNECT BY PRIOR employee_id = manager_id 
   AND salary > commission_pct 
ORDER BY last_name

 
LAST_NAME EMPLOYEE_ID MANAGER_ID
Abel 174 149
Abel 174 149
Abel 174 149
Ande 166 147
Ande 166 147
Ande 166 147
Atkinson 130 121
Austin 105 103
Baer 204 101
Baida 116 114
Banda 167 147
Banda 167 147
Banda 167 147
Bates 172 148
Bates 172 148
Bates 172 148
Bell 192 123
Bernstein 151 145
Bernstein 151 145
Bernstein 151 145
Bissot 129 121
Bloom 169 148
Bloom 169 148
Bloom 169 148
Bull 185 121
Cabrio 187 121
Cambrault 154 145
Cambrault 154 145
Cambrault 154 145
Cambrault 148 100
Cambrault 148 100
Chen 110 108
Chung 188 122
Colmenares 119 114
Davies 142 124
De Haan 102 100
Dellinger 186 121
Dilly 189 122
Doran 160 146
Doran 160 146
Doran 160 146
Ernst 104 103
Errazuriz 147 100
Errazuriz 147 100
Everett 193 123
Faviet 109 108
Fay 202 201
Feeney 197 124
Fleaur 181 120
Fox 170 148
Fox 170 148

Rows 1 - 50

Statement 3
Using the LEVEL Pseudocolumn: Examples The following statement returns all employees in hierarchical order. The root row is defined to be the employee whose job is AD_VP. The child rows of a parent row are defined to be those who have the employee number of the parent row as their manager number.
```
SELECT LPAD(' ',2*(LEVEL-1)) || last_name org_chart, 
        employee_id, manager_id, job_id
    FROM hr.employees
    START WITH job_id = 'AD_VP' 
    CONNECT BY PRIOR employee_id = manager_id
```

LAST_NAME	EMPLOYEE_ID	MANAGER_ID
Abel	174	149
Abel	174	149
Abel	174	149
Ande	166	147
Ande	166	147
Ande	166	147
Atkinson	130	121
Austin	105	103
Baer	204	101
Baida	116	114
Banda	167	147
Banda	167	147
Banda	167	147
Bates	172	148
Bates	172	148
Bates	172	148
Bell	192	123
Bernstein	151	145
Bernstein	151	145
Bernstein	151	145
Bissot	129	121
Bloom	169	148
Bloom	169	148
Bloom	169	148
Bull	185	121
Cabrio	187	121
Cambrault	154	145
Cambrault	154	145
Cambrault	154	145
Cambrault	148	100
Cambrault	148	100
Chen	110	108
Chung	188	122
Colmenares	119	114
Davies	142	124
De Haan	102	100
Dellinger	186	121
Dilly	189	122
Doran	160	146
Doran	160	146
Doran	160	146
Ernst	104	103
Errazuriz	147	100
Errazuriz	147	100
Everett	193	123
Faviet	109	108
Fay	202	201
Feeney	197	124
Fleaur	181	120
Fox	170	148
Fox	170	148

Statement 4

This SQL SELECT statement does not select employees with the job FI_MAN, and does show an indented level connect by query result.

SELECT LPAD(' ',2*(LEVEL-1)) || last_name org_chart, 
        employee_id, manager_id, job_id
    FROM hr.employees
    WHERE job_id != 'FI_MGR'
    START WITH job_id = 'AD_VP' 
    CONNECT BY PRIOR employee_id = manager_id

Statement 5

The following query uses the LEVEL pseudocolumn to select only the first two levels of the management hierarchy.

SELECT LPAD(' ',2*(LEVEL-1)) || last_name org_chart, 
employee_id, manager_id, job_id 
FROM HR.employees
START WITH job_id = 'AD_PRES' 
CONNECT BY PRIOR employee_id = manager_id AND 
      LEVEL <= 2

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database