SQL语句的NOT IN如何改进效率? ( 积分: 12 )

L

LJH1978

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-04-06

• #1

表A

AID NAME BID.....
现在我要找出所有AID的记录号没在BID中的记录,我用如下SQL语句

select * from 表A WHERE AID NOT IN(select BID from 表A )
表中有一万条记录左右,运行时为什么很慢,要近十分钟?这种语句效率太低,有没有什么好的办法

 

T

TYZhang

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-04-06

• #2

select a.*
from 表A a,
(select aid from (select aid from 表A
union all
select bid from 表A) t
group by aid having count(*)=1
)TT
where a.aid=tt.aid

 

L

luckywzy

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-04-06

• #3

SELECT 表A.* FROM 表A LEFT JOIN 表B ON AID=BID
WHERE BID IS NULL

 

L

LJH1978

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-04-06

• #4

多人接受答案了。