学步园

1. exists的使用

Exists 用于只能用于子查询，可以替代in，若匹配到结果，则退出内部查询，并将条件标志为true，传回全部结果资料。in 不管匹配到匹配不到都全部匹配完毕，使用exists 可以将子查询结果定为常量，不影响查询效果，而且效率高。

In和exists对比：若子查询结果集比较小，优先使用in，若外层查询比子查询小，优先使用exists。因为若用in，则oracle 会优先查询子查询，然后匹配外层查询，若使用exists，则oracle 会优先查询外层表，然后再与内层表匹配。最优化匹配原则，拿最小记录匹配大记录。

（这里使用的是oracle数据库自带的测试表emp和dept）

使用in：

select e.ename,e.job,e.sal from emp e where e.deptno in<br /> (select d.deptno from dept d where d.deptno=10);

使用exists：

select e.ename,e.job,e.sal from emp e where exists<br /> (select 1 from dept d where e.deptno=d.deptno and d.deptno=10);

结果：

ENAME JOB SAL<br /> ---------- --------- ---------<br /> MILLER CLERK 1300.00<br /> KING PRESIDENT 5000.00<br /> CLARK MANAGER 2450.00

2 not exists的使用

与exists 含义相反，也在子查询中使用，取出不满足条件的，与not in有一定的区别，注意有时候not exists不能完全替代not in。select e.ename,e.job,e.sal from emp e where not exists<br /> (select 1 from dept d where e.deptno=d.deptno and d.deptno=20);</p> <p>ENAME JOB SAL<br /> ---------- --------- ---------<br /> MILLER CLERK 1300.00<br /> KING PRESIDENT 5000.00<br /> CLARK MANAGER 2450.00<br /> JAMES CLERK 950.00<br /> TURNER SALESMAN 1500.00<br /> BLAKE MANAGER 2850.00<br /> MARTIN SALESMAN 1250.00<br /> WARD SALESMAN 1250.00<br /> ALLEN SALESMAN 1600.00

3.not exists和not in不同的情况：

drop table test1;<br /> drop table test2;<br /> create table test1(a number,b number);<br /> create table test2(a number,b number);<br /> insert into test1 values(1,1);<br /> insert into test1 values(1,2);<br /> insert into test2 values(1,1);<br /> insert into test2 values(1,null);<br /> commit;

--使用not exists找出test1不在test2中的记录，都用b字段匹配<br /> select * from test1 t1 where not exists<br /> (select 1 from test2 t2 where t1.b=t2.b);</p> <p> A B<br /> ---------- ----------<br /> 1 2

--使用not in,没有结果返回,因为test2中的b有null<br /> select * from test1 t1 where t1.b not in (select t2.b from test2 t2);</p> <p> A B<br /> ---------- ----------<br />

--用相关子查询,结果同not exists<br /> select * from test1 t1 where t1.b not in (select t2.b from test2 t2 where t1.b=t2.b);</p> <p> A B<br /> ---------- ----------<br /> 1 2