SELECT * FROM tb2 WHERE tb2.Field1 Not IN (SELECT tb1.Field1 FROM tb1)上面这个带有NOT IN的查询为什么这么慢?SELECT * FROM tb2 WHERE tb2.Field1 IN (SELECT tb1.Field1 FROM tb1)而带有IN的查询速度就很快.我现在要用到NOT IN这种查询,该怎么办呀?
或者使用INNER JOIN 比如:SELECT tb2.* FROM tb2, tb1 WHERE tb2.Field1 = tb1.Field1
SELECT * FROM tb2 WHERE tb2.Field1 IN (SELECT tb1.Field1 FROM tb1) 改为---> SELECT * FROM tb2 WHERE EXISTS (SELECT * FROM tb1 WHERE tb1.Field1 =tb2.Field1 )
按照大家说的,我将 SELECT * FROM tb2 WHERE tb2.Field1 Not IN (SELECT tb1.Field1 FROM tb1) 改成了: select * from tb2 where not exists(select * from tb1 where tb1.field1=tb2.field1) 但速度还是非常慢,怎么回事呀??
你给的是存在的情况,这个我试过了,速度可以。但是不存在就很慢,怎么回事?代码如下: select * from tb2 where not exists(select * from tb1 where tb1.field1=tb2.field1)
比如:SELECT tb2.* FROM tb2, tb1 WHERE tb2.Field1 = tb1.Field1
WHERE tb2.Field1 IN (SELECT tb1.Field1 FROM tb1)
改为--->
SELECT * FROM tb2
WHERE EXISTS (SELECT * FROM tb1 WHERE tb1.Field1 =tb2.Field1 )
选没的时候用not exists。
sql 的查询是查到有没有,这样当然快了。
SELECT * FROM tb2 WHERE tb2.Field1 Not IN (SELECT tb1.Field1 FROM tb1)
改成了:
select * from tb2 where not exists(select * from tb1 where tb1.field1=tb2.field1)
但速度还是非常慢,怎么回事呀??
select * from tb2 where not exists(select * from tb1 where tb1.field1=tb2.field1)