semijoin链接进行subquery unnesting.

最新推荐文章于 2022-05-02 00:47:02 发布

weixin_30418341

最新推荐文章于 2022-05-02 00:47:02 发布

阅读量85

点赞数

原文链接：http://www.cnblogs.com/zhaoyangjian724/p/3798099.html

版权

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.

转载于:https://www.cnblogs.com/zhaoyangjian724/p/3798099.html