(转)关于semi-join/anti-join的一点探讨

  原博客地址：http://space.itpub.net/15480802/viewspace-703260

 

create table dept (deptno number(5), dname varchar2(20));
create table emp (deptno number(5), name varchar2(20));
insert into dept values(1,'IT');
insert into dept values(2,'HR');
insert into dept values(3,'Marketplace');
insert into emp values(1,'justin1');
insert into emp values(1,'justin2');
insert into emp values(2,'justin3');
commit;

Semi-join
通常出现在使用了exists或in的sql中，所谓semi-join即在两表关联时，当第二个表中存在一个或多个匹配记录时，返回第一个表的记录；
与普通join的区别在于semi-join时，第一个表里的记录最多只返回一次；

例1
现在要查询出职员不为空的部门
普通的表连接会返回重复值
SQL> select d.deptno,d.dname from dept d, emp e where e.deptno = d.deptno;
 
DEPTNO DNAME
------ --------------------
     1 IT
     1 IT
     2 HR
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     3 |   114 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN         |      |     3 |   114 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |     3 |    75 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP  |     3 |    39 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
而使用semi-join时候，不会返回重复记录
SQL> select d.deptno,d.dname from dept d where d.deptno in (select e.deptno from emp e);
 
DEPTNO DNAME
------ --------------------
     1 IT
     2 HR
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     2 |    76 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN SEMI    |      |     2 |    76 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |     3 |    75 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP  |     3 |    39 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

 

Anti-join
而anti-join则与semi-join相反，即当在第二张表没有发现匹配记录时，才会返回第一张表里的记录；
当使用not exists/not in的时候会用到，两者在处理null值的时候会有所区别

何时选择anti-join
1 使用not in且相应列有not null约束
2 not exists，不保证每次都用到anti-join
当无法选择anti-join时，oracle常会采用filter替代

例2
查询职员为空的部门
普通sql
SQL> select d.deptno,d.dname from dept d
  2  minus
  3  select d.deptno,d.dname from dept d where d.deptno in (select e.deptno from emp e);
 
DEPTNO DNAME
------ --------------------
     3 Marketplace
-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     3 |   189 |    12  (75)| 00:00:01 |
|   1 |  MINUS               |      |       |       |            |          |
|   2 |   SORT UNIQUE        |      |     3 |    75 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL | DEPT |     3 |    75 |     3   (0)| 00:00:01 |
|   4 |   SORT UNIQUE        |      |     3 |   114 |     8  (25)| 00:00:01 |
|*  5 |    HASH JOIN         |      |     3 |   114 |     7  (15)| 00:00:01 |
|   6 |     TABLE ACCESS FULL| EMP  |     3 |    39 |     3   (0)| 00:00:01 |
|   7 |     TABLE ACCESS FULL| DEPT |     3 |    75 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

使用anti join
SQL> select d.deptno,d.dname from dept d
  2  where d.deptno not in (select deptno from emp);
 
DEPTNO DNAME
------ --------------------
     3 Marketplace
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     3 |   114 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN ANTI NA |      |     3 |   114 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |     3 |    75 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP  |     3 |    39 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

注：倘若subquery返回一条null值，则整个not in都变为false，即不返回任何值
If the subquery of a NOT IN clause returns at least one row with a null value, the entire NOT IN clause evaluates to false for all rows
SQL> select d.deptno,d.dname from dept d where d.deptno not in (select deptno from emp);

    DEPTNO DNAME
---------- --------------------
         3 Marketplace

SQL> insert into emp values(null,'NULL');

1 row created.

SQL> commit;

Commit complete.

SQL> select d.deptno,d.dname from dept d where d.deptno not in (select deptno from emp);

no rows selected

 

而换成not exists时候，hash join anti na变成了hash join anti

select d.deptno,d.dname from dept d where not exists (select deptno from emp where emp.deptno = d.deptno);
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     3 |   114 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN ANTI    |      |     3 |   114 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |     3 |    75 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP  |     3 |    39 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
Not exists与not in的区别在于not exists不受null值影响

注意
Oracle在解析sql时候会尽可能的把子查询转换为表连接
Oracle在以下情况不会使用semi-join
1、    使用distinct或者union
2、    Exists/in子查询中使用了or

Hash_sj/merge_sj/nl_sj都是关于semi-join的一些hint

评论

1 楼 u011165335 2018-06-03  

distinct不影响使用半连接