select * from A where not Exists (select * from B Where BH=A.BH)
select BH from A where not exists(select BH from B where b.BH=A.BH)
--根据你表中的记录情况,有时候下面的写法可能效率not exists的写法select a.bh from A left join B on b.BH=A.BH where b.bh is null
select BH from A where not exists(select BH from B where b.BH=A.BH)
select BH from A where not exists(select BH from B where b.BH=A.BH) 大家给出的答案都是这个啊,我试了一下, 好象差不多。
select BH from A where not exists(select BH from B where b.BH=A.BH) 这个的效率应该比 select BH from A where not in (select BH from B) 高些的!!
设编号为主键 select bh from a where bh not in(select bh from b where bh=a.bh)
select BH from A where not Exists (select * from B Where BH=A.BH)
如果先进行一下排序,然后再SELECT估计会快一点吧
exists比not in 的效率高吗?又学到了~
exists當然比not in要高了 select BH from A where not exists(select BH from B where b.BH=A.BH)
一、不能用not in,因为它会全表扫描 二、建议在两个表中的bh都建上索引 三、试一下: select BH from A minus select BH from B;
同意 zjcxc(邹建): select a.bh from A left join B on b.BH=A.BH where b.bh is null
select BH from A where not Exists (select * from B Where BH=A.BH) select BH from A where not in (select BH from B)首先,我是推薦使用 exists 類的,因為他涉及的邏輯少,一般效率高(大數據量的時候), 但in 邏輯多,所以能實現很多exists不能實現的功能(因為exists 隻能判斷是否問題) 而in 是個包含問題)
大家给出的答案都是这个啊,我试了一下,
好象差不多。
这个的效率应该比
select BH from A where not in (select BH from B)
高些的!!
select bh from a where bh not in(select bh from b where bh=a.bh)
select BH from A where not exists(select BH from B where b.BH=A.BH)
二、建议在两个表中的bh都建上索引
三、试一下:
select BH from A minus select BH from B;
select a.bh from A left join B on b.BH=A.BH where b.bh is null
select BH from A where not in (select BH from B)首先,我是推薦使用 exists 類的,因為他涉及的邏輯少,一般效率高(大數據量的時候),
但in 邏輯多,所以能實現很多exists不能實現的功能(因為exists 隻能判斷是否問題)
而in 是個包含問題)