如何更快地执行SQ​​L“NOT IN”查询？ [英] How can I perform a SQL 'NOT IN' query faster?

问题描述

我有一个电子邮件地址表（EMAIL）：

  EmailAddress 
 ------- ----- 
 [e-mail] 
 [e-mail] 
 [e-mail] 
 [e-mail] 
  
 
 
 
和一个列入黑名单电子邮件地址的表格（BLACKLIST）：
 code> EmailAddress 
 ------------ 
 [e-mail] 
 [e-mail] 
  
，我想选择EMAIL表中但不在BLACKLIST表中的电子邮件地址。我在做：
  SELECT EmailAddress 
 FROM EMAIL 
 WHERE EmailAddress NOT IN 
 
 SELECT EmailAddress 
 FROM BLACKLIST 
）
  
计数得到非常高的性能是可怕的。
 
 
 
如何更好地做这个？   
解决方案
使用左外连接或不存在子句。
 
 
 
左外连接：
 
 
 选择E.EmailAddress 
从EMAIL E左外部联接BLACKLIST B（E.EmailAddress = B.EmailAddress）
其中B .EmailAddress为null; 
  

不存在：

 从EMAIL E中不存在时选择E.EmailAddress 
 
（从BLACKLIST B中选择EmailAddress，其中B.EmailAddress = E.EmailAddress）
  
 
 
 
 两者是相当通用的SQL解决方案（不依赖于特定的数据库引擎）。我会说，后者是一个有点更高性能（不是很多）。但确实比不在中更有效。
 
 
 
如同评论者所说，你也可以尝试创建一个索引 BLACKLIST（EmailAddress），这将有助于加快查询的执行速度。
 
I have a table (EMAIL) of email addresses:
EmailAddress
------------
[e-mail]
[e-mail]
[e-mail]
[e-mail]
and a table (BLACKLIST) of blacklisted email addresses:
EmailAddress
------------
[e-mail]
[e-mail]
and I want to select those email addresses that are in the EMAIL table but NOT in the BLACKLIST table.  I'm doing:
SELECT EmailAddress
FROM EMAIL
WHERE EmailAddress NOT IN
   (
      SELECT EmailAddress
      FROM BLACKLIST
   )
but when the row counts get very high the performance is terrible.

How can I better do this?  (Assume generic SQL if possible.  If not, assume T-SQL.)
解决方案
You can use a left outer join, or a not exists clause.

Left outer join:
select E.EmailAddress
  from EMAIL E left outer join BLACKLIST B on (E.EmailAddress = B.EmailAddress)
 where B.EmailAddress is null;
Not Exists:
select E.EmailAddress
  from EMAIL E where not exists
         (select EmailAddress from BLACKLIST B where B.EmailAddress = E.EmailAddress)
Both are quite generic SQL solutions (don't depend on a specific DB engine). I would say that the latter is a little bit more performant (not by much though). But definitely more performant than the not in one.

As commenters stated, you can also try creating an index on BLACKLIST(EmailAddress), that should help speed up the execution of your query.

                        
这篇关于如何更快地执行SQ​​L“NOT IN”查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！