sql与in相对

⑴ sql优化——IN和EXISTS谁的效率更高

在SQL编程中，IN和EXISTS是常被使用的子查询方式，它们的功能相似但效率存在差异。通过对比不同场景下的测试，可以发现使用哪一种更为高效。

在测试1中，我们使用了子查询`select flag from B where B_id<100`，结果集为99条。在这个场景下，IN的效率高于EXISTS。IN执行计划中，首先执行A表的查询，并利用索引优化，然后执行B表子查询，同样使用索引。而EXISTS的执行计划则先取A表所有记录，然后逐行关联B表子查询，使用Block Nested Loop优化。

测试2中，子查询`select flag from B where B_id>100`，结果集为299899条。这次EXISTS的效率比IN高。两者在索引使用上与第一次测试一致，但当子查询结果集较大而外部表较小时，EXISTS的Block Nested Loop开始发挥优势，查询效率优于IN。

综上所述，并不能断言IN或EXISTS的效率更高，具体情况需要具体分析。IN的执行原理是先查询内表得到结果集，再与外表匹配，不管子查询是否有数据，都会进行全部匹配，这在内表数据量较大时效率较低。而EXISTS则是先对外表进行循环查询，只在匹配到数据时返回结果，适用于子查询数据量较大而外表数据量较小时。

结论是，子查询结果集较大时使用EXISTS，而结果集较小时使用IN可能更有效。对于更详细的信息和深入理解，可以参考墨天轮原文链接。

⑵ sql exist和in的区别及查询效率比较

SQL查询中in和exists的区别分析

select * from A where id in (select id from B);

select * from A where exists (select 1 from B where A.id=B.id);

对于以上两种情况，in是在内存里遍历比较，而exists需要查询数据库，所以当B表数据量较大时，exists效率优于in。

1、IN()语句内部工作原理

IN()只执行一次，它查出B表中的所有id字段并缓存起来。之后，检查A表的id是否与B表中的id相等，如果相等则将A表的记录加入结果集中，直到遍历完A表的所有记录。
它的查询过程类似于以下过程：

List resultSet={};

Array A=(select * from A);

Array B=(select id from B);for(int i=0;i<A.length;i++)

{

for(int j=0;j<B.length;j++) {

if(A[i].id==B[j].id) {
resultSet.add(A[i]); break;
}
}
}return resultSet;

可以看出，当B表数据较大时不适合使用in()，因为它会B表数据全部遍历一次

• 例1：A表有10000条记录，B表有1000000条记录，那么最多有可能遍历10000*1000000次，效率很差。

• 例2：A表有10000条记录，B表有100条记录，那么最多有可能遍历10000*100次，遍历次数大大减少，效率大大提升。

结论：IN()适合B表比A表数据小的情况

2、EXISTS()语句内部工作原理

exists()会执行A.length次，它并不缓存exists()结果集，因为exists()结果集的内容并不重要，重要的是其内查询语句的结果集空或者非空，空则返回false，非空则返回true。
它的查询过程类似于以下过程：

• List resultSet={};

• Array A=(select * from A);

• for(int i=0;i<A.length;i++)

• { if(exists(A[i].id) { //执行select 1 from B where B.id=A.id是否有记录返回



• resultSet.add(A[i]);


• }


• }return resultSet;



当B表比A表数据大时适合使用exists()，因为它没有那么多遍历操作，只需要再执行一次查询就行。

• 例1：A表有10000条记录，B表有1000000条记录，那么exists()会执行10000次去判断A表中的id是否与B表中的id相等。

• 例2：A表有10000条记录，B表有100000000条记录，那么exists()还是执行10000次，因为它只执行A.length次，可见B表数据越多，越适合exists()发挥效果。

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

结论：EXISTS()适合B表比A表数据大的情况

3、使用情况分析

当A表数据与B表数据一样大时，in与exists效率差不多，可任选一个使用。

在插入记录前，需要检查这条记录是否已经存在，只有当记录不存在时才执行插入操作，可以通过使用 EXISTS 条件句防止插入重复记录。

• insert into A (name,age) select name,age from B

• where not exists (select 1 from A where A.id=B.id);




EXISTS与IN的使用效率的问题，通常情况下采用exists要比in效率高，因为IN不走索引。但要看实际情况具体使用：

• IN适合于外表大而内表小的情况；

• EXISTS适合于外表小而内表大的情况。

4、关于EXISTS：

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

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

语法： EXISTS subquery

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

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

结论：

• select * from A where exists (select 1 from B where A.id=B.id)



EXISTS(包括 NOT EXISTS )子句的返回值是一个boolean值。 EXISTS内部有一个子查询语句(SELECT ... FROM...),我将其称为EXIST的内查询语句。其内查询语句返回一个结果集, EXISTS子句根据其内查询语句的结果集空或者非空，返回一个布尔值。

一种通俗的可以理解为：将外查询表的每一行，代入内查询作为检验，如果内查询返回的结果取非空值，则EXISTS子句返回TRUE，这一行行可作为外查询的结果行，否则不能作为结果。

分析器会先看语句的第一个词，当它发现第一个词是SELECT关键字的时候，它会跳到FROM关键字，然后通过FROM关键字找到表名并把表装入内存。接着是找WHERE关键字，如果找不到则返回到SELECT找字段解析，如果找到WHERE，则分析其中的条件，完成后再回到SELECT分析字段。最后形成一张我们要的虚表。

WHERE关键字后面的是条件表达式。条件表达式计算完成后，会有一个返回值，即非0或0，非0即为真(true)，0即为假(false)。同理WHERE后面的条件也有一个返回值，真或假，来确定接下来执不执行SELECT。

分析器先找到关键字SELECT，然后跳到FROM关键字将STUDENT表导入内存，并通过指针找到第一条记录，接着找到WHERE关键字计算它的条件表达式，如果为真那么把这条记录装到一个虚表当中，指针再指向下一条记录。如果为假那么指针直接指向下一条记录，而不进行其它操作。一直检索完整个表，并把检索

java">

出来的虚拟表返回给用户。EXISTS是条件表达式的一部分，它也有一个返回值(true或false)。




作者：IronM
链接：https://www.jianshu.com/p/f212527d76ff
来源：简书
着作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

⑶ SQL 中的 in 与 not in、exists 与 not exists 的区别以及性能分析

SQL中，in和exists，以及not in和not exists在使用上有显着的区别，并且性能受到表大小和查询条件的影响。

1. 对于in和exists：

in通常通过hash连接操作，效率取决于两个表的大小，当表相当时，两者性能相近。如果子查询表大，用exists；反之，子查询表小，用in。如表A（小表）与表B（大表）的查询。

2. not in与not exists：

not in与逻辑上的not exists不完全等同，误用可能导致错误。not in通常全表扫描，效率较低，除非子查询字段有非空限制。not exists则利用关联子查询，可以利用索引，无论表大小都更优。

3. in与=的区别：

两者在某些情况下结果相同，但in更强调集合匹配，而=则更为精确。

4. EXISTS的性能分析：

当子查询结果集小，主表大且有索引时，in效率更高。反之，主表小、子表大且有索引时，exists更优。

5. NOT IN与NOT EXISTS：

两者在某些需求下不能互换，NOT EXISTS通常更推荐，尤其是在基于成本的优化中。具体使用取决于主查询和子查询的大小及字段约束。

总结来说，in和exists的选择基于内外表的大小和索引，而not in和not exists则要考虑字段约束和性能优化。优化SQL时，需根据实际场景灵活运用，没有绝对的效率高低，应根据具体情况进行调整。

⑷ 在SQL语句中，in和exist的区别是什么

in是把外表和内表作hash连接，而exists是对外表作loop循环。

⑸ SQL技巧：IN&EXISTS

在构建SQL查询语句时，IN & NOT IN和EXISTS & NOT EXISTS是两种常见且重要的条件语句。本文将详细介绍Oracle数据库中这两种操作的基本原理及其潜在的性能影响。

IN操作符允许我们在指定的一系列值中匹配数据。例如，查询ID为10, 20或30的员工记录，或表1的员工记录和表2的ID与score相同的记录。IN操作符检查目标列值是否匹配给定列表中的任意值。

EXISTS操作符用于对子查询进行布尔测试。如果子查询返回至少一个记录，则EXISTS返回TRUE，否则返回FALSE。适用于查找存在匹配项的记录，例如表1的员工记录和表2的ID与score相同的记录。

NOT IN和NOT EXISTS与IN和EXISTS操作相对应，分别表示否定的IN和EXISTS条件。

除了IN、EXISTS、NOT IN和NOT EXISTS，INNER JOIN也是一种实现相同功能的方法，但具体效果取决于查询需求、数据集大小、索引设置和数据库版本。

在性能比较方面，IN、EXISTS和INNER JOIN方法的具体效率取决于查询结果集大小、索引设置和数据库版本。使用执行计划分析可以直观地比较不同方法的性能。

总结，IN & NOT IN 和 EXISTS & NOT EXISTS在Oracle数据库中是强大的工具。理解它们的运作方式和适用场景有助于进行有效的查询设计。选择哪种方法取决于特定需求、数据环境和性能测试结果。熟练掌握和灵活运用这些SQL操作符，能够提升SQL运用水平。