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


WITH RCTE_DEPT(id,root,parent_id) AS(
  SELECT id,parent_id, id
  FROM dept
  UNION ALL
  SELECT dept.id,root,RCTE_DEPT.parent_id
  FROM dept
  JOIN RCTE_DEPT ON
    dept.parent_ID = RCTE_DEPT.id)
SELECT * FROM RCTE_DEPT ORDER BY ID


ID
ROOT
PARENT_ID


1
null
1

2
null
1

2
1
2

3
1
3

3
null
1

4
null
1

4
1
2

4
2
4

5
1
2

5
null
1

5
2
5

6
1
3

6
3
6

6
null
1

7
1
3

7
3
7

7
null
1


WITH RCTE_DEPT(id,root,parent_id) AS(
  SELECT id,parent_id, id
  FROM dept
  UNION ALL
  SELECT dept.id,root,RCTE_DEPT.parent_id
  FROM dept
  JOIN RCTE_DEPT ON
    dept.parent_ID = RCTE_DEPT.id)
SELECT emp.id as ID_E, RCTE_DEPT.root as ROOT, RCTE_DEPT.parent_id as DEPT
FROM emp
JOIN RCTE_DEPT ON emp.DEPT = RCTE_DEPT.id 

ORDER BY ID_E ASC, ROOT ASC, DEPT ASC


ID_E
ROOT
DEPT


1
1
2

1
null
1

2
1
2

2
null
1

3
1
3

3
null
1

4
1
2

4
2
4

4
null
1

5
1
2

5
2
5

5
null
1

6
1
3

6
3
6

6
null
1

7
1
3

7
3
7

7
null
1

8
1
2

8
null
1

9
1
3

9
null
1

11
1
2

11
2
5

11
null
1

12
1
3

12
3
6

12
null
1

13
1
3

13
3
7

13
null
1

14
1
2

14
null
1

15
1
3

15
null
1

16
1
2

16
2
4

16
null
1

17
1
2

17
2
5

17
null
1
ID	ROOT	PARENT_ID
1	null	1
2	null	1
2	1	2
3	1	3
3	null	1
4	null	1
4	1	2
4	2	4
5	1	2
5	null	1
5	2	5
6	1	3
6	3	6
6	null	1
7	1	3
7	3	7
7	null	1