数据库：SQLServer中in和 exists函数用法笔记

一、IN 用法

确定指定的值是否与子查询或列表中的数据相匹配。

1.1 语法格式

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

1.2 参数说明

test_expression

为任意有效的SQL表达式。

subquery

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

expression[ ,... n ]

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

注意：在 IN 子句的括号中显式包括数量非常多的值（数以千计，以逗号分隔）可能会消耗资源并返回错误 8623 或 8632。 若要解决这一问题，可以将这些项存储于某个表的 IN 列表中，然后在 IN 子句中使用 SELECT 嵌套查询。

1.3 用法示例：

select  * from t_user where name in ('aaa','bbb','ccc');Select name from students where studentId not in(select stuid from studentScore where score>90);

二、EXISTS 用法

2.1 语法：EXISTS subquery

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

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

2.2 示例：

-- null  返回全表select * from T_user where  exists (select null);等同于：select * from T_user-- 常用写法select * from T_user where exists (select userid from score)

三、IN 和 EXISTS 区别

3.1 IN列子

select * from tb1 where id in(select id from tb2)

解释：上面的查询语句使用了in语句,in()只执行一次,它查出tb2表中的所有id字段并缓存起来.之后,检查tb1表的id是否与tb2表中的id相等,如果相等则将A表的记录加入结果集中,直到遍历完tb1表的所有记录.

转换为编程语言如下：

List List=[];Array A=(select * from tb1);Array B=(select id from tb2);for(int i=0;i<A.length;i++) {   for(int j=0;j<B.length;j++) {      if(A[i].id==B[j].id) {         List.add(A[i]);          break;       }   } } return List;

如:tb1表有10000条记录,tb2表有1000000条记录,那么最多有可能遍历10000*1000000次,效率很差.

再如:tb1表有10000条记录,tb2表有100条记录,那么最多有可能遍历10000*100次,遍历次数大大减少,效率大大提升.

结论:in()适合tb2表比tb1表数据小的情况

3.2 EXISTS例子

select a.* from A a where exists(select 1 from tb2 b where a.id=b.id)

以上查询使用了exists语句,exists()会执行A.length次,它并不缓存exists()结果集,因为exists()结果集的内容并不重要,重要的是结果集中是否有记录,如果有则返回true,没有则返回false.

转换为编程语言如下：

List List=[];Array A=(select * from tb1);for(int i=0;i<A.length;i++) {     if(exists(A[i].id) {      //执行select 1 from tb2 b where b.id=a.id是否有记录返回         List.add(A[i]);     }}return List ;

当tb2表比tb1表数据多的时候，适合使用exists(),因为它没有那么遍历操作,只需要再执行一次查询就行.

如:tb1表有10000条记录,B表有1000000条记录,那么exists()会执行10000次去判断tb1表中的id是否与tb2表中的id相等.

如:tb1表有10000条记录,B表有100000000条记录,那么exists()还是执行10000次,因为它只执行tb1.length次,可见tb2表数据越多,越适合exists()发挥效果.

再如:tb1表有10000条记录,tb2表有100条记录,那么exists()还是执行10000次,还不如使用in()遍历10000*100次,因为in()是在内存里遍历比较,而exists()需要查询数据库,我们都知道查询数据库所消耗的性能更高,而内存比较很快.

结论:exists()适合tb2表比tb1表数据大的情况

总结：EXISTS与IN的使用效率的问题，通常情况下采用exists要比in效率高，但要看实际情况具体使用：IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。

IT技术分享社区

个人博客网站：https://programmerblog.xyz