sql----改写 in ,exists,not in ,not exists

建立测试用表 

drop table a;<pre name="code" class="html">select a.* from a,b where a.id=b.id;1a2b2b

drop table b;create table a (id number,name varchar2(20));create table b (id number,name varchar2(20));insert into a values (1,'a');insert into a values (2,'b');insert into a values (3,'c');insert into a values (3,'d');insert into b values (1,'a');insert into b values (2,'c');insert into b values (2,'d');insert into b values (null,'e');insert into a values (null,'f');

INselect * from a where id in (select * from b) ;1a2b

改成exists

select * from a where exists (select 1 from b where a.id=b.id) ;

改成连接的写法

select a.* from a,b where a.id=b.id;1a2b<span style="color:#ff0000;">2b</span>

因为b中的数据，存在重复--导致数据不一致，改成连接写法时，需要去重

not inselect * from a where id not in (select id from b) ;如果b值中有空值，那么返回空select * from a where id not in (select id from b where b.id is not null) ;3d3c换成 not exists写法select * from a where not exists (select 1 from b where a.id=b.id) ;null f3d3cselect a.* from a ,B WHERE A.ID=B.ID(+) and  B.ID IS NULL;nullf3d3c

在NOT IN 改写的时候，返回表中的数据，是否存在空值，会影响最终结果