i want to find all managers names and their all managers name in oracle SQL

Question

select e.last_name, ee.last_name 
from employees e join employees ee on (e.manager_id = ee.employee_id) 
where last_name in (select last_name 
                    from employees 
                    where employee_id in (select distinct manager_id 
                                          from employees
                                         )
                   );

Answer 1

If I understood you correctly, you want to select managers only.

The following example is based on Scott's schema.

Here are all employees, along with their managers (hierarchically) so that it is easier to spot the managers.

SQL> select lpad(' ', (level - 1) * 2) || ename ename
  2  from emp
  3  start with mgr is null
  4  connect by prior empno = mgr;

ENAME
------------------------------------------------------
KING              -- manager
  JONES           -- manager
    FORD          -- manager
      SMITH       -- employee
  BLAKE           -- manager
    ALLEN         -- employee
    WARD          -- employee
    MARTIN        -- employee
    TURNER        -- employee
    JAMES         -- employee
  CLARK           -- manager
    MILLER        -- employee

12 rows selected.

SQL>

Additional WHERE clause returns managers only:

SQL> select lpad(' ', (level - 1) * 2) || ename ename
  2  from emp
  3  where empno in (select mgr from emp)
  4  start with mgr is null
  5  connect by prior empno = mgr;

ENAME
--------------------------------------------------------
KING
  JONES
    FORD
  BLAKE
  CLARK

SQL>

Substituting what is getting selected with SYS_CONNECT_BY_PATH, we'll get a different output:

SQL> select sys_connect_by_path(ename, ' / ') path
  2  from emp
  3  where empno in (select mgr from emp)
  4  start with mgr is null
  5  connect by prior empno = mgr;

PATH
----------------------------------------------------
 / KING
 / KING / JONES
 / KING / JONES / FORD
 / KING / BLAKE
 / KING / CLARK

SQL>

Or, following your steps, with a self-join of the EMP table, we get

SQL> select m1.ename manager, m2.ename his_manager
  2  from emp m1 join emp m2 on m1.mgr = m2.empno
  3  where m1.empno in (select mgr from emp);

MANAGER    HIS_MANAGER
---------- -------------
FORD       JONES
CLARK      KING
BLAKE      KING
JONES      KING

SQL>

Pick the one that suits you best. I believe other members will suggest other options.