T-SQL 之 IN和EXISTS的区别和性能比较

官网地址：IN、EXISTS

subquery 或 expression 使用 IN 或 NOT IN 与 test_expression 比较后返回的所有空值都将返回 UNKNOWN。将空值与 IN 或 NOT IN 一起使用会产生意外结果。

在 IN 子句中包括数量非常多的值（数以千计）可能会消耗资源并返回错误 8623 或 8632。若要解决这一问题，请将这些项存储于某个表的 IN 列表中。
错误 8623：

查询处理器用尽了内部资源，无法生成查询计划。这种情况很少出现，只有在查询极其复杂或引用了大量表或分区时才会出现。请简化查询。如果您认为该消息的出现纯属错误，请与客户支持服务部门联系，了解详细信息。
错误 8632：

in 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。一直以来认为exists比in效率高的说法是不准确的。
如果查询的两个表大小相当，那么用in和exists差别不大。
如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in：
例如：表A（小表），表B（大表）1：

select * from A where cc in (select cc from B)

效率低，用到了A表上cc列的索引；

select * from A where exists(select cc from B where cc=A.cc)

效率高，用到了B表上cc列的索引。
相反的2：

select * from B where cc in (select cc from A)

效率高，用到了B表上cc列的索引；

select * from B where exists(select cc from A where cc=B.cc)

效率低，用到了A表上cc列的索引。

not in 和not exists
如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。

in和exists性能比较

• 1. 外大内小的情况:
     history.tb_stk_cap_chg 记录数 > 100,000,000
     history.tb_stk_cap_chg_test 记录数 = 20
     外大内小用in效率极低,用exists效率很高

• 2. 外小内大的情况:
     history.tb_stk_cap_chg 记录数 > 100,000,000
     history.tb_stk_cap_chg_test 记录数 = 1,000,000
     外小内大时使用in比exists效率更高

由此得出结论: exists适合内小外大的查询，in适合内大外小的查询