semijoin链接进行subquery unnesting.

drop table emp1;
drop table dept1;
create table emp1 as select * from emp;
create table dept1 as select * from dept;

SQL> select e.empno, e.deptno
  from emp1 e
 where e.deptno in (select d.deptno from dept1 d where d.loc = 'CHICAGO');  2    3 

     EMPNO     DEPTNO
---------- ----------
      7900    30
      7844    30
      7698    30
      7654    30
      7521    30
      7499    30

6 rows selected.

SQL> select e.empno, e.deptno
from emp1 e, dept1 d
where e.deptno = d.deptno
and d.loc = 'CHICAGO';  2    3    4 

     EMPNO     DEPTNO
---------- ----------
      7499    30
      7521    30
      7