使用not in和 not exists这两sql为啥结果不一样？

[csu7433rj]

select b.* from tmp_ddn a,tmp_ddn b
where b.order_item_id = a.root_order_item_id and
not exists( select * from tmp_ddn_guoji where sn_97 = a.sn_97 )
and not exists( select * from tmp_ddn_vpn where sn_97 = a.sn_97 )
and not exists( select * from tmp_ddn_shangwang where sn_97 = a.sn_97 ))
和

select b.* from tmp_ddn a,tmp_ddn b
where b.order_item_id = a.root_order_item_id and
a.sn_97 not in ( select sn_97 from tmp_ddn_guoji )
and a.sn_97 not in ( select sn_97 from tmp_ddn_vpn )
and a.sn_97 not in ( select sn_97 from tmp_ddn_shangwang )

这两语句的意思应该相同吧？为什么结果不同？

[bobfang]

有空值是会不一样。

[kingofworl]

sn_97 有空值

[wwwzjb]

not exists比not in 的性能要高,也是官方推荐的用法

[csu7433rj]

能否具体说说呀？

[csu7433rj]

“有空值是会不一样。”

那要怎样修改才能让他们结果一样呢？？？

[多壮志]

如果不想选择出非空的部分，在第一语句中加 A.SN_97 IS NOT NULL 即可。
select b.* from tmp_ddn a,tmp_ddn b
where b.order_item_id = a.root_order_item_id and （A.SN_97 IS NOT NULL）
not exists( select * from tmp_ddn_guoji where sn_97 = a.sn_97 )
and not exists( select * from tmp_ddn_vpn where sn_97 = a.sn_97 )
and not exists( select * from tmp_ddn_shangwang where sn_97 = a.sn_97 ))

[csu7433rj]

应该不是空值的问题，我试了第一句sql返回600多条记录，第二条返回的记录为空。高手再帮我看看阿，我就想弄到底问题出在哪

[csu7433rj]

搞清楚了
把
select b.* from tmp_ddn a,tmp_ddn b
where b.order_item_id = a.root_order_item_id and
a.sn_97 not in ( select sn_97 from tmp_ddn_guoji )
and a.sn_97 not in ( select sn_97 from tmp_ddn_vpn )
and a.sn_97 not in ( select sn_97 from tmp_ddn_shangwang )
改成
select b.* from tmp_ddn a,tmp_ddn b
where b.order_item_id = a.root_order_item_id and
a.sn_97 not in ( select sn_97 from tmp_ddn_guoji )
and a.sn_97 not in ( select sn_97 from tmp_ddn_vpn where sn_97 is not null )
and a.sn_97 not in ( select sn_97 from tmp_ddn_shangwang where sn_97 is not null )
结果就一样了

[zhangzm1117]

good 不错