Posted on 2006-04-18 16:01
Terry的Blog 阅读(22991)
评论(5) 编辑 收藏 所属分类:
oracle
在Oracle SQL中取数据时有时要用到in 和 exists 那么他们有什么区别呢？
1 性能上的比较
比如Select * from T1 where x in ( select y from T2 )
执行的过程相当于:
select *
from t1, ( select distinct y from t2 ) t2
where t1.x = t2.y;
相对的
select * from t1 where exists ( select null from t2 where y = x )
执行的过程相当于:
for x in ( select * from t1 )
loop
if ( exists ( select null from t2 where y = x.x )
then
OUTPUT THE RECORD
end if
end loop
表 T1 不可避免的要被完全扫描一遍
分别适用在什么情况?
以子查询 ( select y from T2 )为考虑方向
如果子查询的结果集很大需要消耗很多时间，但是T1比较小执行( select null from t2 where y = x.x )非常快，那么exists就比较适合用在这里
相对应得子查询的结果集比较小的时候就应该使用in.
2 含义上的比较
在标准的scott/tiger用户下
|
|
EMPNO
|
ENAME
|
JOB
|
MGR
|
HIREDATE
|
SAL
|
COMM
|
DEPTNO
|
|
1
|
7499
|
ALLEN
|
SALESMAN
|
7698
|
1981/02/20
|
1600.00
|
300.00
|
30
|
|
2
|
7521
|
WARD
|
SALESMAN
|
7698
|
1981/02/22
|
1250.00
|
500.00
|
30
|
|
3
|
7566
|
JONES
|
MANAGER
|
7839
|
1981/04/02
|
2975.00
|
|
20
|
|
4
|
7654
|
MARTIN
|
SALESMAN
|
7698
|
1981/09/28
|
1250.00
|
1400.00
|
30
|
|
5
|
7698
|
BLAKE
|
MANAGER
|
7839
|
1981/05/01
|
2850.00
|
|
30
|
|
6
|
7782
|
CLARK
|
MANAGER
|
7839
|
1981/06/09
|
2450.00
|
|
10
|
|
7
|
7788
|
SCOTT
|
ANALYST
|
7566
|
1987/04/19
|
3000.00
|
|
20
|
|
8
|
7839
|
KING
|
PRESIDENT
|
|
1981/11/17
|
5000.00
|
|
10
|
|
9
|
7844
|
TURNER
|
SALESMAN
|
7698
|
1981/09/08
|
1500.00
|
0.00
|
30
|
|
10
|
7876
|
ADAMS
|
CLERK
|
7788
|
1987/05/23
|
1100.00
|
|
20
|
|
11
|
7900
|
JAMES
|
CLERK
|
7698
|
1981/12/03
|
950.00
|
|
30
|
|
12
|
7902
|
FORD
|
ANALYST
|
7566
|
1981/12/03
|
3000.00
|
|
20
|
|
13
|
7934
|
MILLER
|
CLERK
|
7782
|
1982/01/23
|
1300.00
|
|
10
|
执行
SQL> select count(*) from emp where empno not in ( select mgr from emp );
COUNT(*)
----------
0
SQL> select count(*) from emp T1
2 where not exists ( select null from emp T2 where t2.mgr = t1.empno ); -- 这里子查询中取出null并没有什么特殊作用，只是表示取什么都一样。
COUNT(*)
----------
8
结果明显不同，问题就出在MGR=null的那条数据上。任何值X not in (null) 结果都不成立。
用一个小例子试验一下:
select * from dual where dummy not in ( NULL ) -- no rows selected
select * from dual where NOT( dummy not in ( NULL ) ) --no rows selected
知觉上这两句SQL总有一句会取出数据的，但是实际上都没有。SQL中逻辑表达式的值可以有三种结果（true false null）而null相当于false.