为什么SQL“ NOT IN”这么慢吗？ [英] Why SQL "NOT IN" is so slow?

问题描述

下面是我的SQL代码：

Below is my SQL code:

select count(1) 
from customers 
where id in(
    select custid
    from accounts
    where sid in(72,73,74,75,76,77,78,79)
) 
and id not in(
    select custid 
    from accounts 
    where sid in(80,81)
);

表已正确索引。可以重写此代码以获得更好的性能吗？

Tables are indexed properly. Can this code be rewritten for better performance?

推荐答案

您还可以尝试使用EXISTS：

You could also try EXISTS:

select count(1) 
from customers c
where exists (
    select 1
    from accounts a
    where sid in(72,73,74,75,76,77,78,79)
    and a.custid = c.custid
) 
and not exists (
    select 1
    from accounts a
    where sid in(80,81)
    and a.custid = c.custid
);

以下内容可能会有所帮助： SQL中EXISTS和IN之间的区别？

This might be helpful read: Difference between EXISTS and IN in SQL?

这篇关于为什么SQL“ NOT IN”这么慢吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！