Not sure how to write connect by clause

Question

I have the following query to find employees who are not managers in the EMP table of oracle

 select * from emp e1
 where not exists (select null from emp e2 
                    where e2.mgr=e1.empno)

I need output using start with connect by clause , thus avoiding self join

Answer 1

There is a function, CONNECT_BY_ISLEAF(), which indicates whether a given row in a hierarchical query is a leaf node. In the EMP table, employees who are not managers will be leaf nodes.

So, we can use this function in a nested hierarchical query to filter the non-managers:

select empno, mgr, ename 
from (
    select empno, mgr, ename, CONNECT_BY_ISLEAF cbi
    from emp 
    start with mgr is null 
    connect by prior empno = mgr
) where cbi = 1
/

Oracle has several neat functions for interrogating hierarchies. Find out more.