关于oracle中的半连接(r3笔记55天)

jeanron100

发布于 2018-03-15 10:11:53

6460

发布于 2018-03-15 10:11:53

表的连接在sql语句中尤为重要。外连接，内连接，半连接，反连接等等各种连接，看似简单的一个连接里面还是有不少的细节的。对于sql调优来说也是很重要的。像下面的形式的sql就属于半连接，使用了in子句，对于exists的实现也是属于半连接。

--in半连接
SQL> select dname from dept dept where deptno in (select deptno  from emp emp);
DNAME
--------------
RESEARCH
SALES
ACCOUNTING

--exists半连接
SQL> select dname from dept dept where exists (select null from emp emp  where emp.deptno=dept.deptno)
  2  /
DNAME
--------------
RESEARCH
SALES
ACCOUNTING

可能对于上面两种连接大家不以为然，认为把需要用到的表直接放在from子句后效果是一致的，答案也不是肯定的。比如下面的形式，可能输出的结果就多了很多。大概14条记录，但是通过半连接的方式会输出3行记录。

SQL>  select dept.dname from dept  dept,emp empwhere dept.deptno=emp.deptno;  
DNAME
--------------
RESEARCH
SALES
SALES
RESEARCH
SALES
SALES
ACCOUNTING
RESEARCH
ACCOUNTING
SALES
RESEARCH
SALES
RESEARCH
ACCOUNTING
14  rows selected.

所以说如果要得到一个相同的输出结果，还是需要distinct+inner join

SQL> select distinct dept.dname from dept dept,emp emp where dept.deptno=emp.deptno;

DNAME -------------- ACCOUNTING RESEARCH SALES 对于半连接的可替换实现,大体有以下几种方式

--使用集合
select dept.dname from dept dept,
(select deptno from  dept
intersect
select deptno from emp emp)b
where dept.deptno=b.deptno  ;
DNAME
--------------
ACCOUNTING
RESEARCH
SALES

--使用any
SQL> select dept.dname from dept dept where deptno=any(select deptno from emp emp);
DNAME
--------------
RESEARCH
SALES
ACCOUNTING

--使用distinct和内连接
SQL> select distinct emp.deptno  from dept dept,emp emp
    where dept.deptno=emp.deptno;
     DEPTNO
----------
        30
        20
        10
SQL> select  distinct dept.deptno from dept  dept,emp emp
        where dept.deptno=emp.deptno;
     DEPTNO
----------
        30
        20
        10

大体说了下关于半连接的一些实现，可能在实际的使用中，最直观的感受还是通过执行计划来看到。启用了半连接，在执行计划中会有semi的字样。