? 原博客地址：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值的时候会有所区别

<