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;
1	a
2	b
2	b

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');

IN
select * from a where id in (select * from b) ;
1	a
2	b

改成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;
1	a
2	b
<span style="color:#ff0000;">2	b</span>

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

not in

select * 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) ;
3	d
3	c
换成 not exists写法
select * from a where not exists (select 1 from b where a.id=b.id) ;
null 	f
3	d
3	c
select a.* from a ,B WHERE A.ID=B.ID(+) and  B.ID IS NULL;
null	f
3	d
3	c

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