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
      7654    30
      7698    30
      7844    30
      7900    30

6 rows selected.

此时子查询被改写成关联,结果完全等价,是因为d.deptno上是Unique索引

那如果d.deptno不唯一呢？
SQL> select * from dept1;

    DEPTNO DNAME   LOC
---------- -------------- -------------
 10 ACCOUNTING   NEW YORK
 20 RESEARCH   DALLAS
 30 SALES   CHICAGO
 40 OPERATIONS   BOSTON
 30 SALES   CHICAGO

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
      7499    30
      7521    30
      7521    30
      7654    30
      7654    30
      7698    30
      7698    30
      7844    30
      7844    30
      7900    30

     EMPNO     DEPTNO
---------- ----------
      7900    30

12 rows selected.

改写成关联后结果就翻倍了得去从
SQL> select e.empno, e.deptno
from emp1 e, dept1 d
where e.deptno = d.deptno
and d.loc = 'CHICAGO'
group by e.empno, e.deptno  2    3    4    5  ;

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

6 rows selected.