sql1 select * from tbl t1 where t1.code not in ( select t2.code from tbl2 )sql2 select t1.code from tbl t1 minus select t2.code tbl t2 t1 和t2 数据量都比较大,表示的意思是,t1.code - t2.code 数据对剪 但是 sql1无法查处结果,但是sql2能够查询出结果请问 原因
应该是没问题吧。有什么现象吗,不出意外的话结果应该是一样的 只是如果数据量大的话用not in 肯定会很慢才对
因为table2中的code值存在Null,所以就查询不出数据 --测试 create table ta (id int,name varchar2(10)); insert into ta select 1,'aa' from dual; insert into ta select 2,'bb' from dual; insert into ta select null,'cc' from dual; commit;create table tb (id int,name varchar2(10)); insert into tb select 1,'aa' from dual; insert into tb select null,'bb' from dual; commit; --查询不出数据 select * from ta where id not in (select id from tb); --可以查询出数据 select * from ta minus select * from tb;--解决方法:用not exists代替not in select * from ta a where not exists(select 1 from ta b where a.id=b.id)--解决方法:删除null值或者过滤掉null值 delete from tb where id is null;select * from ta where id not in (select id from tb);
只是如果数据量大的话用not in 肯定会很慢才对
--测试
create table ta (id int,name varchar2(10));
insert into ta select 1,'aa' from dual;
insert into ta select 2,'bb' from dual;
insert into ta select null,'cc' from dual;
commit;create table tb (id int,name varchar2(10));
insert into tb select 1,'aa' from dual;
insert into tb select null,'bb' from dual;
commit;
--查询不出数据
select * from ta
where id not in (select id from tb);
--可以查询出数据
select * from ta
minus
select * from tb;--解决方法:用not exists代替not in
select * from ta a
where not exists(select 1 from ta b where a.id=b.id)--解决方法:删除null值或者过滤掉null值
delete from tb where id is null;select * from ta
where id not in (select id from tb);