SQL中in和exists的区别

SQL中in和exists的区别

in 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。

一、in的语法

test_expression [ NOT ] IN
(
subquery
| expression [ ,...n ]
)

参数

test_expression

是任何有效的 Microsoft® SQL Server™ 表达式。

subquery

是包含某列结果集的子查询。该列必须与 test_expression 有相同的数据类型。

expression [,...n]

一个表达式列表，用来测试是否匹配。所有的表达式必须和 test_expression 具有相同的类型。

结果类型

布尔型

结果值

如果 test_expression 与 subquery 返回的任何值相等，或与逗号分隔的列表中的任何 expression 相等，那么结果值就为 TRUE。否则，结果值为 FALSE。

二、EXISTS

EXISTS用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False
EXISTS 指定一个子查询，检测 行 的存在。

语法： EXISTS subquery

参数： subquery 是一个受限的 SELECT 语句 (不允许有 COMPUTE 子句和 INTO 关键字)。

结果类型： Boolean 如果子查询包含行，则返回 TRUE ，否则返回 FLASE 。

三、IN 与 EXISTS 的区别

IN
确定给定的值是否与子查询或列表中的值相匹配。

EXISTS
指定一个子查询，检测行的存在。

分别适用在什么情况?

以子查询 ( select y from T2 )为考虑方向
 

如果子查询的结果集很大需要消耗很多时间，但是T1比较小执行( select null from t2 where y = x.x )非常快，那么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要快。