Semi-join通常出现在使用了exists或in的
sql中，所谓semi-join即在两表关联时，当第二个表中存在一个或多个匹配记录时，返回第一个表的记录；
与普通join的区别在于semi-join时，第一个表里的记录最多只返回一次；
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;
例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则与semi-join相反，即当在第二张表没有发现匹配记录时，才会返回第一张表里的记录；
当使用not exists/not in的时候会用到，两者在处理null值的时候会有所区别
例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 |
---------------------------------------------------------------------------
而换成not exists时候，hash join anti na变成了hash join anti
---------------------------------------------------------------------------
| 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
当前位置：
论坛首页 »
自由讨论区 »
MySQL, Oracle及数据库讨论区
»
发起投票
技术讨论
加好友
发消息
2011-8-1 21:34:25 |
赞(
操作
