关于EXISTS的使用及效率

    本文参考了不过的ORACLE博客http://www.cnblogs.com/yf520gn/archive/2009/01/12/1374359.html 后根据自己的理解来写的。

创建两张表T1、T2,其中T1的LOCLINE列中的某些值存在于T2的LOCATION中
create table T1
(
  ASSETNUM VARCHAR2(50) not null,
  LOCLINE  VARCHAR2(50)
)

create table T2
(
  LOCATION    VARCHAR2(36),
  MANAGEDEPT  VARCHAR2(50),
  GRADE       VARCHAR2(50),
  DESCRIPTION VARCHAR2(50)
)
1. 理解EXISTS的及执行流程

EXISTS subquery
参数subquery 是一个受限的 SELECT 语句 (不允许有 COMPUTE 子句和 INTO 关键字)。
结果类型
Boolean
结果值
如果子查询包含行，则返回 TRUE。

select T1.*
from T1
where exists
(select NULL from T2  where T1.Locline = T2.Location )

可以这样理解上面的SQL语句
可以理解为:
  for x in ( select * from t1 )
  loop
      if ( exists (select null from t2 where t2.location = x.locline)
      then
        OUTPUT THE RECORD
      end if
  end loop
   我的理解是这样的：首先从T1中取出一个locline的值，假设这个值为XX, 然后执行select NULL from T2  where T2.Location = XX,如果T2.Location存在一个XX的值，那么此时就会得到一条记录，此时的where exists(select NULL from T2  where T1.Locline = T2.Location )就为TRUE；那么最后就会查询出 T1中locline的值为XX的那些记录，即最终执行了这样一条SQL语句 select T1.* from T1 where T1.LOCLINE = XX；然后继续从T1中取出下一个locline的值......
对于exists后的select null 这是无关紧要的，(select T2.* from T2  where T1.Locline = T2.Location )或者 (select T2.Location from T2  where T1.Locline = T2.Location )或者(select 1 from T2  where T1.Locline = T2.Location )等等都是可以的，我们只是要得出某条记录在T2表是否存在，并不需要查询T2表中的任何列的数据，所以使用select null,但是是否在性能上有所影响，还没有研究过。

 

2.exists和in的性能区别
exist 会利用索引来检索，而in不用
select T1.*
from T1
where exist
(select NULL from T2  where T1.Locline = T2.Location )

select T1.*
from T1
where t1.locline in
(select t2.location from t2 )

    表 T1 不可避免的要被完全扫描一遍，如果T1表的较小，而T2较大时则用EXISTS效率会比较高，并且如果表T2上有索引时，查询时会用到T2上的索引。如果是T1表较大，而T2表较小时，则采用IN。
   其实我们区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询，所以我们会以驱动表的快速返回为目标，那么就会考虑到索引及结果集的关系了

3.not exists和not in的比较
sql1:
select count(*)
from T1
where not exists
(select null from T2  where T1.Locline = T2.Location )

sql2:
select count(*)
from T1
where t1.locline not in
(select t2.location from t2 )

NOT EXISTS 与 NOT IN 不能完全互相替换，看具体的需求。如果选择的列可以为空，则不能被替换。
我测试的t1表中有952条记录的locline的值为NULL，其他的值在t2表中都存在；
sql1运行的结果是:952, 而sql2的运行结果是0
之所以是这样的结果，对于sql1,在T1中取出了一条locline为null的记录，很显然执行子查询not exists (select null from T2  where T1.Locline = T2.Location )时返回结果为TRUE；而对于sql2,先执行子查询select t2.location from t2，然后在T1中取出了一条locline为null的记录，显然locline为null的值在t2表中不存在。在这种情况下要使得

sql1和sql2运行结果一样，可以这样修改sql2
select count(t1.assetnum)
from T1
where  t1.locline not in
(select t2.location from t2 )
union
select count(t1.assetnum)
from t1
where t1.locline is null

 

如果需要排除locline为空的记录,则可以这样修改sql1
select count(*)
from T1
where not exists
(select null from T2  where T1.Locline = T2.Location ) and t1.locline is not

null
对于not in 和 not exists的性能区别：
  not in 只有当子查询中，select 关键字后的字段有not null约束或者有这种暗示时用not in,另外如果主查询中表大，子查询中的表小但是记录多，则应当使用not in
  如果主查询表中记录少，子查询表中记录多，并有索引，可以使用not exists.

 

以上是我个人对exist使用的理解。