Oracle IN & EXISTS

20-MAR-12 HR @ hdb>

select first_name,
       last_name,
       department_id 
from   employees e1 
where  exists(select * from employees e2 where e2.manager_id = e1.employee_id);

FIRST_NAME           LAST_NAME                 DEPARTMENT_ID
-------------------- ------------------------- -------------
Michael              Hartstein                            20
Shelley              Higgins                             110
Steven               King                                 90
Neena                Kochhar                              90
Lex                  De Haan                              90
Alexander            Hunold                               60
Nancy                Greenberg                           100
Den                  Raphaely                             30
Matthew              Weiss                                50
Adam                 Fripp                                50
Payam                Kaufling                             50

FIRST_NAME           LAST_NAME                 DEPARTMENT_ID
-------------------- ------------------------- -------------
Shanta               Vollman                              50
Kevin                Mourgos                              50
John                 Russell                              80
Karen                Partners                             80
Alberto              Errazuriz                            80
Gerald               Cambrault                            80
Eleni                Zlotkey                              80
18 rows selected.

20-MAR-12 HR @ hdb>

select first_name,
       last_name,
       department_id 
from   employees e1 
where  e1.employee_id in(select manager_id from employees e2);

FIRST_NAME           LAST_NAME                 DEPARTMENT_ID
-------------------- ------------------------- -------------
Michael              Hartstein                            20
Shelley              Higgins                             110
Steven               King                                 90
Neena                Kochhar                              90
Lex                  De Haan                              90
Alexander            Hunold                               60
Nancy                Greenberg                           100
Den                  Raphaely                             30
Matthew              Weiss                                50
Adam                 Fripp                                50
Payam                Kaufling                             50

FIRST_NAME           LAST_NAME                 DEPARTMENT_ID
-------------------- ------------------------- -------------
Shanta               Vollman                              50
Kevin                Mourgos                              50
John                 Russell                              80
Karen                Partners                             80
Alberto              Errazuriz                            80
Gerald               Cambrault                            80
Eleni                Zlotkey                              80
18 rows selected.