sql中的 in、exist、not in、not exist

原文链接:https://www.cnblogs.com/snn0605/p/5901634.html

1、in和exists

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

子查询表小的用in;

例如:表A(小表),表B(大表)

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列的索引。

相反的:

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列的索引。

2、not in 和not exists

not in 逻辑上不完全等同于not exists,如果你误用了not in,小心你的程序存在致命的BUG,请看下面的例子:

create table #t1(c1 int,c2 int);
create table #t2(c1 int,c2 int);
insert into #t1 values(1,2);
insert into #t1 values(1,3);
insert into #t2 values(1,2);
insert into #t2 values(1,null);
select * from #t1 where c2 not in(select c2 from #t2);  -->执行结果:无
select * from #t1 where not exists(select 1 from #t2 where #t2.c2=#t1.c2)  -->执行结果:1  3
 

正如所看到的,not in出现了不期望的结果集,存在逻辑错误。如果看一下上述两个select 语句的执行计划,也会不同,后者使用了hash_aj,所以,请尽量不要使用not in(它会调用子查询),而尽量使用not exists(它会调用关联子查询)。如果子查询中返回的任意一条记录含有空值,则查询将不返回任何记录。如果子查询字段有非空限制,这时可以使用not in,并且可以通过提示让它用hasg_aj或merge_aj连接。

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

3、in 与 = 的区别

select name from student where name in('zhang','wang','zhao');
select name from student where name='zhang' or name='wang' or name='zhao'

的结果是相同的。

### 回答1: 'NOT IN'和'NOT EXIST'是SQL语句的两种条件操作符,用于查询符合某个条件的记录并检查其是否存在于另一个表。其,'NOT IN'用于从一个表选出不匹配另一个表的记录,而'NOT EXIST'则用于查询一个表不存在于另一个表的记录。 ### 回答2: SQL语句NOT IN和NOT EXISTS是两种常用的条件查询方法,它们都用于在查询数据时过滤掉不必要的结果,但是它们又有一些区别。 首先,NOT IN操作符用于在WHERE子句排除一个子查询所返回的结果集的值,它的语法如下: SELECT column_name(s) FROM table_name WHERE column_name NOT IN (SELECT column_name FROM table_name WHERE condition); 这个语句检索出所有列出的列,除了在子查询返回的值。其,NOT IN操作符要求子查询返回一个结果集,否则运行时会出现错误。此外,NOT IN通常性能不是很高,因为在执行查询之前,它需要将子查询的结果集全部提取出来,然后再将主查询的结果集与其进行比较。 相比之下,NOT EXISTS用于过滤掉那些在子查询结果集无法找到对应记录的行。NOT EXISTS的语法如下: SELECT column_name(s) FROM table_name WHERE NOT EXISTS (SELECT column_name FROM table_name WHERE condition); 这个语句检索出与子查询不匹配的行,因为当子查询返回空结果集时,NOT EXISTS将返回TRUE,表示指定的条件不存在,因此,它的运行效率比NOT IN要高一些。此外,NOT EXISTS在处理外连接时更加灵活,因为它不会使用外连接将数据组合在一起,因此,它更容易处理复杂的查询条件。 综上所述,NOT IN和NOT EXISTS都是用于过滤结果集的SQL操作符,但是在使用时需要根据具体情况来选择合适的操作符。当需要排除一个子查询返回的结果集的值时,可以使用NOT IN,而当需要检查是否不存在与子查询匹配的行时,则应使用NOT EXISTS。 ### 回答3: SQL语句的NOT IN和NOT EXISTS是用来比较查询结果和指定值或结果集之间的差异。尽管它们的作用相似,但它们实际上是被用于不同的场景。 在SQL查询语句,NOT IN是一个比较运算符,用于指定一个查询结果集,其结果集包含查询条件指定的任何一个值。这种比较方式特别适用于已知在大数据集排除特定值或一组值的情况。比如,我们需要查询在一个表没有出现的某些值,则可以使用NOT IN语句。 另一方面,NOT EXISTS指令是一个布尔运算符,用于确定查询结果集如果为空时返回的逻辑真值。也就是说,如果指定的查询结果集为空,则NOT EXISTS将返回TRUTH值,否则,将返回FLASE值。这个命令通常用于过滤查询结果集不存在满足特定查询条件的情况。例如,我们要查询不存在一本图书的作者,则可以使用NOT EXISTS语句。 因此,NOT IN和NOT EXISTS确实有相似的用途,它们都提供了一种简单的方式来限制结果集,来只包含需要的值。这就要求我们在实际应用根据不同的查询场景,灵活地选择使用其的一种方式。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值