Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE dept(
  id number,
  parent_id number
);

CREATE TABLE emp (
  id number,
  dept number
);

Insert into emp (id,dept) 
SELECT 1,2 FROM dual UNION ALL
SELECT 2,2 FROM dual UNION ALL
SELECT 3,3 FROM dual UNION ALL
SELECT 4,4 FROM dual UNION ALL
SELECT 5,5 FROM dual UNION ALL
SELECT 6,6 FROM dual UNION ALL
SELECT 7,7 FROM dual UNION ALL
SELECT 8,2 FROM dual UNION ALL
SELECT 9,3 FROM dual UNION ALL
SELECT 11,5 FROM dual UNION ALL
SELECT 12,6 FROM dual UNION ALL
SELECT 13,7 FROM dual UNION ALL
SELECT 14,2 FROM dual UNION ALL
SELECT 15,3 FROM dual UNION ALL
SELECT 16,4 FROM dual UNION ALL
SELECT 17,5 FROM dual;
    
    
    
    
16 rows affected

INSERT INTO dept(id, parent_id)
SELECT 1,null FROM dual UNION ALL
SELECT 2,1 FROM dual UNION ALL
SELECT 3,1 FROM dual UNION ALL
SELECT 4,2 FROM dual UNION ALL
SELECT 5,2 FROM dual UNION ALL
SELECT 6,3 FROM dual UNION ALL
SELECT 7,3 FROM dual;
7 rows affected

SELECT *
FROM emp;


ID
DEPT


1
2

2
2

3
3

4
4

5
5

6
6

7
7

8
2

9
3

11
5

12
6

13
7

14
2

15
3

16
4

17
5


SELECT connect_by_root(dept.id) AS dept_id, 
       CONNECT_BY_ROOT(dept.parent_id) AS parent_id, 
       emp.id AS id_e
FROM emp
RIGHT JOIN dept ON emp.dept = dept.id
CONNECT BY PRIOR dept.id = dept.parent_id


DEPT_ID
PARENT_ID
ID_E


2
1
1

2
1
4

2
1
17

2
1
16

2
1
11

2
1
5

3
1
15

3
1
6

3
1
7

3
1
12

3
1
13

2
1
14

2
1
4

2
1
17

2
1
16

2
1
11

2
1
5

3
1
9

3
1
6

3
1
7

3
1
12

3
1
13

2
1
8

2
1
4

2
1
17

2
1
16

2
1
11

2
1
5

2
1
2

2
1
4

2
1
17

2
1
16

2
1
11

2
1
5

3
1
3

3
1
6

3
1
7

3
1
12

3
1
13

4
2
4

5
2
17

4
2
16

5
2
11

5
2
5

6
3
6

7
3
7

6
3
12

7
3
13

1
null
null

1
null
1

1
null
4

1
null
17

1
null
16

1
null
11

1
null
5

1
null
15

1
null
6

1
null
7

1
null
12

1
null
13

1
null
14

1
null
4

1
null
17

1
null
16

1
null
11

1
null
5

1
null
9

1
null
6

1
null
7

1
null
12

1
null
13

1
null
8

1
null
4

1
null
17

1
null
16

1
null
11

1
null
5

1
null
2

1
null
4

1
null
17

1
null
16

1
null
11

1
null
5

1
null
3

1
null
6

1
null
7

1
null
12

1
null
13

DEPT_ID	PARENT_ID	ID_E
2	1	1
2	1	4
2	1	17
2	1	16
2	1	11
2	1	5
3	1	15
3	1	6
3	1	7
3	1	12
3	1	13
2	1	14
2	1	4
2	1	17
2	1	16
2	1	11
2	1	5
3	1	9
3	1	6
3	1	7
3	1	12
3	1	13
2	1	8
2	1	4
2	1	17
2	1	16
2	1	11
2	1	5
2	1	2
2	1	4
2	1	17
2	1	16
2	1	11
2	1	5
3	1	3
3	1	6
3	1	7
3	1	12
3	1	13
4	2	4
5	2	17
4	2	16
5	2	11
5	2	5
6	3	6
7	3	7
6	3	12
7	3	13
1	null	null
1	null	1
1	null	4
1	null	17
1	null	16
1	null	11
1	null	5
1	null	15
1	null	6
1	null	7
1	null	12
1	null	13
1	null	14
1	null	4
1	null	17
1	null	16
1	null	11
1	null	5
1	null	9
1	null	6
1	null	7
1	null	12
1	null	13
1	null	8
1	null	4
1	null	17
1	null	16
1	null	11
1	null	5
1	null	2
1	null	4
1	null	17
1	null	16
1	null	11
1	null	5
1	null	3
1	null	6
1	null	7
1	null	12
1	null	13