※ 引述《flakchen (flak)》之铭言： : : 就MS-SQL而言，IN 的效果并不差，差的是NOT IN : LEFT OUTER JOIN的效率也好不到哪里，如果必须取得「不存在於...」 : 的资料，请用NOT EXISTS : : SELECT TB1.fd1 : FROM TB1 : WHERE NOT EXISTS (SELECT top 1 1 FROM TB2 WHERE TB1.fd1 = TB2.fd2 ) : : : -- :

※ 发信站: 批踢踢实业坊(ptt.cc) : ◆ From: 220.132.166.116 : → webberhan:你的根据是?可以分享一下推论过程吗? 11/20 13:46 : 推 jerryen:因为那是outer join吧 11/20 17:27 请参考http://www.sql-server-performance.com/transact_sql.asp If you currently have a query that uses NOT IN, which offers poor performance because the SQL Server optimizer has to use a nested table scan to perform this activity, instead try to use one of the following options instead, all of which offer better performance: Use EXISTS or NOT EXISTS Use IN Perform a LEFT OUTER JOIN and check for a NULL condition [6.5, 7.0, 2000] Updated 10-30-2003 ***** When you have a choice of using the IN or the EXISTS clause in your Transact-SQL, you will generally want to use the EXISTS clause, as it is usually more efficient and performs faster. [6.5, 7.0, 2000] Updated 10-30-2003 不过再根据我我日常要处理500万-5000万笔的大型资料表，时常要 TRY AND ERROR来找出最佳效能解的累积经验： LEFT JOIN会导致SQL要耗费更多硬碟I/O来储存两个资料表的连结资料， 所以如果查询结果不需要第二个资料表的资料，能不JOIN就不要JOIN， 尤其是LEFT/RIGHT OUTER JOIN IN跟EXISTS比起来，没有他说的那麽烂，通常我感觉不到两者效能有何差别 --

※ 发信站: 批踢踢实业坊(ptt.cc) ◆ From: 210.64.110.97