oracle中in,not in和exists,not exists之间的区别

11/13/2009来源:Oracle教程人气:3860

       一直听到的都是说尽量用exists不要用in,因为exists只判断存在而in需要对比值,所以exists比较快,但看了看网上的一些东西才发现根本不是这么回事。
下面这段是抄的
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

从我的角度来说,in的方式比较直观,exists则有些绕,而且in可以用于各种子查询,而exists好像只用于关联子查询(其他子查询当然也可以用,可惜没意义)。
由于exists是用loop的方式,所以,循环的次数对于exists影响最大,所以,外表要记录数少,内表就无所谓了,而in用的是hash join,所以内表如果小,整个查询的范围都会很小,如果内表很大,外表如果也很大就很慢了,这时候exists才真正的会快过in的方式。
         下面这段还是抄的

not in 和not exists
如果查询语句使用了not in 那么内外表都进行全表扫描,没有用到索引;
而not extsts 的子查询依然能用到表上的索引。
所以无论那个表大,用not exists都比not in要快。
          也就是说,in和exists需要具体情况具体分析,not in和not exists就不用分析了,尽量用not exists就好了。



下有一个表-电视剧   
  TvPlay(title,   year,   studioname,   男主角,   女主角),   
    
  查询出被重复拍摄1次以上的电视剧名,(如射雕,倚天屠龙)   
  select   title   
  from   TvPlay   tp   
  where   year   >   
            (select   year   
              from       TvPlay   
              where     title   =   tp.title   
            );   
    
  简单子查询只在()中执行一次,而上面()中的语句是一个关联子查询,需要根据外层的条件多次执行。