in 和exists

in 和exists

in是把外表和内表作hash 连接，而exists 是对外表作loop 循环，每次loop 循环再对内表进行查询。

如果查询的两个表大小相当，那么用in 和exists 差别不大。

如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in：

e.g

新建测试表

create table t1 as select  seq_t1.nextval id ,object_name,owner from dba_objects;

create index idx_t1 on t1( id);

create table t2 as select seq_t2.nextval id ,object_name,owner from dba_objects;

begin

  for i in 1 ..100 loop

      INSERT INTO t2 select seq_t2.nextval id,object_name,owner from dba_objects;

      COMMIT;

  end loop; 

end;

create index index_t2 on t2(id);

/*t2表是大表，in时放里面速度较慢,exists 时放里面速度较快*/

select count (*) from t1 where id in ( select id from t2);--慢

select count (*) from t1 where exists (select 1 from t2 where t1.id= t2.id);--快

not in ,not exists

1.not in 与 not exists 不等价

e.g

新建测试表

create table t3(id number(1));
insert into t3 values(2);
insert into t3 values(3);
insert into t3 values(4);
insert into t3 values(2);
insert into t3 values(null);
commit;
create index idx_t3 on t3(id);

然后再次执行两个SQL：

select * from t1 where not exists (select 1 from t3 where t3.id= t1.id);

select * from t1 where id not in ( select id from t3);

select * from t1 where id not in ( 1,2 ,3, 4,null );

select * from t1 where id<> 1 and id<>2 and id<>3 and id<>4 and id<>null ;

因为id <> null是一个永假式，所以最终查出的结果肯定也就是空了。

由此可以得出结论：只要not in的子查询中包含空值，那么最终的结果就为空

2.not in 与 not exists 的性能

update t3 set id= 9 where id is null ;

/*内表大，外表小时*/

select count (*) from t2 where id not in (select id from t3); --快

但是not in 会造成内表的全表扫描，当内表很大时，性能非常低效

select count (*) from t2 where not exists ( select 1 from t3 where t3.id= t2.id); --慢