Problem:

*Different ways of querying hierarchical data through SQL for querying subset of branch information.*

**Case study :** 

Employees table :

```
desc employees
Name           Null     Type         
-------------- -------- ------------ 
EMPLOYEE_ID    NOT NULL NUMBER(6)    
FIRST_NAME              VARCHAR2(20) 
LAST_NAME      NOT NULL VARCHAR2(25) 
MANAGER_ID              NUMBER(6)    
DEPARTMENT_ID           NUMBER(4)    

```

**Premises:** 

President is an employee who doesn't have the manager i.e. null.

There are leaf nodes for eg developers who manages none i.e. employee\_id not in manager\_id.

There are 1rst line managers who manages developers, 2nd line managers who managers 1rst line and so on.....

I could all the managers from below query:

```
SELECT manager_id,
  employee_id
FROM EMPLOYEES o
WHERE EXISTS
  (SELECT * FROM EMPLOYEES i WHERE o.employee_id = i.manager_id)
AND manager_id IS NOT NULL
ORDER BY o.manager_id,
  o.EMPLOYEE_ID;

```

and president from below:

```
SELECT manager_id,
employee_id
    FROM EMPLOYEES o
    WHERE EXISTS
      (SELECT * FROM EMPLOYEES i WHERE o.employee_id = i.manager_id)
    AND manager_id IS NULL
    ORDER BY o.manager_id,
      o.EMPLOYEE_ID;

```

How should I get the 2nd line managers through `exists` key word ?

How should I get the 2nd line managers without using `exists`?

What are the different ways of getting the hierarchical data ?

And also regarding performance of queries.