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

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

Scroll Down


今天给大家分享一下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表数据小的情况

33.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适合于外表小而内表大的情况。