sql语句中in与exist not in与not exist 的区别
In和NotIn的使用注意事项和区别
In和NotIn的使⽤注意事项和区别总结：NULL值不能⽤⽤来+-*/, 不能⽤来<>, not in否则返回空或NULL即不可以 1. not in （select null），返回空2. not exists 如果条件中有null值，外层查询的null值会被返回。
3. in 和 exists 均会过滤掉条件中null值not In 相当于 <> all,如果 Not In 后⾯跟的是⼦查询的话，⼦查询中只要包含⼀个 null 的返回值，则会造成整个 Not in 字句返回空值，结果就是查询不会返回任何结果。
⽽ in 相当于 =any 的意思,可以有效处理⼦查询中返回空值的情况,返回正确的结果。
------------------------------------------------------------------------------------------------------not in ⽰例：--该例⼦想要返回没有下属的职员的姓名，如果⼦查询中有空值返回的话，则整个查询将没有结果返回SELECT st_nameFROM employees empWHERE emp.employee_id NOT IN (SELECT mgr.manager_id FROM employees mgr)说明：Null Values in a SubqueryThe SQL statement in the slide attempts to display all the employees who do not have anysubordinates. Logically, this SQL statement should have returned 12 rows. However, the SQLstatement does not return any rows. One of the values returned by the inner query is a null value and, therefore, the entire query returns no rowsThe reason is that all conditions that compare a null value result in a null. So whenever null valuesare likely to be part of the resultsset of a subquery, do not use the NOT INoperator. The NOT INoperator is equivalent to <> ALL.---------------------------------------------------------------------------------------------------------in 的⽰例：Notice that the null value as part of the results set of a subquery is not a problem if you use the INoperator. The IN operator is equivalent to =ANY. For example, to display the employees who have subordinates(下属), use the following SQL statement:SELECT st_nameFROM employees empWHERE emp.employee_id IN (SELECT mgr.manager_id FROM employees mgr);---------------------------------------------------------------------------------------------------------Alternatively, a WHERE clause can be included in the subquery to display all employees who do nothave any subordinates:--使⽤ Not In 的话，要注意除掉⼦查询中将要返回的空值SELECT last_nameFROM employeesWHERE employee_id NOT IN(SELECT manager_id FROM employees WHERE manager_id IS NOT NULL);。
sql中in和not not in的用法
sql中in和not not in的用法
在SQL中，IN 和NOT IN 是两个用于条件过滤的子句，它们允许我们在WHERE 子句中测试一个值是否存在于一个子查询或列表中。
1. IN的用法
IN 用于指定多个可能的值。
如果列中的值匹配列表中的任何一个值，条件就为真。
例如，如果我们想从一个名为students的表中选择名为John或Jane的学生，我们可以这样写：
sql
SELECT * FROM students WHERE name IN ('John', 'Jane');
2. NOT IN的用法
NOT IN 与IN 相反。
如果列中的值不匹配列表中的任何值，条件就为真。
例如，如果我们想从students表中选择除了John和Jane之外的所有学生，我们可以这样写：
sql
SELECT * FROM students WHERE name NOT IN ('John', 'Jane');
注意事项
使用IN和NOT IN时，列表中的值应该被正确地用括号括起来，并且每个值之间用逗号分隔。
当列表中的值很多时，使用IN或NOT IN可能会导致性能问题。
在这种情况下，考虑使用连接（JOIN）或其他优化策略。
当使用NOT IN时，如果子查询或列表返回NULL值，可能会导致意外的结果。
例如，如果有一个学生的名字是NULL，并且你执行上面的NOT IN查询，那么这个学生将不会被选中，即使你期望选择所有学生。
总之，IN和NOT IN是SQL中非常有用的条件过滤工具，但在使用时需要注意一些细节和潜在的性能问题。
SQL中IN与EXISTS关键字
SQL中IN与EXISTS关键字 偶遇这样⼀个场景，使⽤IN关键字进⾏检索数据所消耗的时间是使⽤EXISTS关键字进⾏检索数据所消耗的时间的接近30倍。
⼀看差距这么⼤，查阅了⼀本SQL数据，其中也没有介绍多少，不过我们可以从其定义中可以领悟到⼀些差异。
（1）IN关键字：该操作符IN⽤于把⼀个值与⼀个指定列表进⾏⽐较，当被⽐较的值⾄少与列表中的⼀个值相匹配时，它会返回TRUE。
（2）EXISTS关键字：该操作符EXISTS⽤于搜索指定表⾥是否存在满⾜特定条件的记录。
根据这两个关键字作⽤的描述，可知：若是IN⼦句或者EXISTS⼦句都是采⽤SELECT语法检索出来的结果列表进⾏匹配的话，那么在IN⼦句中还要将被⽐较值与结果列表做进⼀步的循环⽐较，当IN中的被⽐较值能够匹配到结果列表中某个值，那么IN⼦句就会返回TRUE，否则的话就会返回FALSE；⽽在EXISTS⼦句中，若SELECT语句检索的结果值不为空，那么EXISTS⼦句直接将该结果集返回，若是检索的结果值为空的，那么EXISTS⼦句就返回空，也就是说EXISTS⼦句返回的就是SELECT语句返回的结果集，不需要再次做⽐较判断了。
-- INSELECT column1FROM table_nameWHERE some_col IN (SELECT column1 FROM table_name WHERE other_col >'xx');-- EXISTSSELECT column1FROM table_nameWHERE EXISTS (SELECT column1 FROM table_name WHERE other_col >'xx'); 上述代码⽰例只是⼀个象征性的对⽐说明，在同⼀个表中进⾏不同条件的多次检索，使⽤IN的⽅式：先根据条件检索出数据，然后some_col与结果列表进⾏循环⽐较；使⽤EXISTS的⽅式：先根据条件检索出数据，然后将该结果集直接返回，作为最终的数据结果了。
SQL中exist与in的区别
SQL中exist与in的区别in 是一个集合运算符.a in {a,c,d,s,d....} P105例子这个运算中,前面是一个元素,后面是一个集合,集合中的元素类型是和前面的元素一样的.而exists是一个存在判断,如果后面的查询中有结果,则exists为真,否则为假.in 运算用在语句中,它后面带的select 一定是选一个字段,而不是select *.比如说你要判断某班是否存在一个名为"小明"的学生,你可以用in 运算:"小明" in (select sname from student)这样(select sname from student) 返回的是一个全班姓名的集合,in用于判断"小明"是否为此集合中的一个数据;同时,你也可以用exists语句:exists (select * from student where sname="小明")select * from 表A where exists(select * from 表B where 表B.id=表A.id)这句相当于select * from 表A where id in (select id from 表B)对于表A的每一条数据,都执行select * from 表B where 表B.id=表A.id的存在性判断,如果表B中存在表A当前行相同的id,则exists为真,该行显示,否则不显示区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。
表AID NAME1 A12 A23 A3表BID AID NAME1 1 B12 2 B23 2 B3表A和表B是１对多的关系 A.ID => B.AIDSELECT ID,NAME FROM A WHERE EXIST (SELECT * FROM B WHERE A.ID=B.AID)执行结果为1 A12 A2原因可以按照如下分析SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=１)--->SELECT * FROM B WHERE B.AID=１有值返回真所以有数据SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=2)--->SELECT * FROM B WHERE B.AID=２有值返回真所以有数据SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=3)--->SELECT * FROM B WHERE B.AID=３无值返回真所以没有数据NOT EXISTS 就是反过来SELECT ID,NAME FROM A WHERE NOT EXIST (SELECT * FROM B WHERE A.ID=B.AID)执行结果为3 A3例子查询选修了全部课程的学生姓名。
mysql查询条件notin和in的区别及原因说明
mysql查询条件notin和in的区别及原因说明先写⼀个SQLSELECT DISTINCT from_idFROM codWHERE cod.from_id NOT IN (37, 56, 57)今天在写SQL的时候，发现这个查的结果不全，少了NULL值的情况，not in 的时候竟然把null也排除了⽤ in 的时候却没有包含null感觉是mysql设计的不合理因为⼀直认为in 和 not in 正好应该互补才是，就像这样查的应该是全部的⼀样：SELECT DISTINCT from_idFROM codWHERE cod.from_id NOT IN (37, 56, 57) or cod.from_id IN (37, 56, 57)结果正如猜测的那样，少了个null后来上⽹上查了下，有⼀个解释挺合理的，即：null与任何值⽐较都是false⽐如from_id有(37, 56, 57,28,null)， not in (37, 56, 57)与28⽐较时是true，所以结果集中出现28，null与not in (37, 56, 57)这个条件⽐较时，结果false，所以不出现在结果集中补充：MySQL条件查询IN和NOT IN左右两侧包含NULL值的处理⽅式题⽬给定⼀个表 tree，id 是树节点的编号， p_id 是它⽗节点的 id 。
+----+------+| id | p\_id |+----+------+| 1 | NULL || 2 | 1 || 3 | 1 || 4 | 2 || 5 | 2 |+----+------+树中每个节点属于以下三种类型之⼀：叶⼦：如果这个节点没有任何孩⼦节点。
根：如果这个节点是整棵树的根，即没有⽗节点。
内部节点：如果这个节点既不是叶⼦节点也不是根节点。
写⼀个查询语句，输出所有节点的编号和节点的类型，并将结果按照节点编号排序。
上⾯样例的结果为：+----+------+| id | TYPE |+----+------+| 1 | Root || 2 | INNER|| 3 | Leaf || 4 | Leaf || 5 | Leaf |+----+------+解释节点 ‘1' 是根节点，因为它的⽗节点是 NULL ，同时它有孩⼦节点 ‘2' 和 ‘3' 。
SQL语句中exists和in的区别
SQL语句中exists和in的区别转⾃https:///liyasong/p/sql_in_exists.html 和 /lick4050312/article/details/4476333表展⽰ 查询中涉及到的两个表，⼀个user和⼀个order表，具体表的内容如下： user表： order表：in ⼀、确定给定的值是否与⼦查询或列表中的值相匹配。
in在查询的时候，⾸先查询⼦查询的表，然后将内表和外表做⼀个笛卡尔积，然后按照条件进⾏筛选。
所以相对内表⽐较⼩的时候，in的速度较快。
具体sql语句如下：1 SELECT2 *3 FROM4 `user`5 WHERE6 `user`.id IN (7 SELECT8 `order`.user_id9 FROM10 `order`11 ) 这条语句很简单，通过⼦查询查到的user_id 的数据，去匹配user表中的id然后得到结果。
该语句执⾏结果如下： 它的执⾏流程是什么样⼦的呢？让我们⼀起来看⼀下。
⾸先，在数据库内部，查询⼦查询，执⾏如下代码：SELECT`order`.user_idFROM`order` 执⾏完毕后，得到结果如下： 此时，将查询到的结果和原有的user表做⼀个笛卡尔积，结果如下： 此时，再根据我们的user.id IN er_id的条件，将结果进⾏筛选（既⽐较id列和user_id 列的值是否相等，将不相等的删除）。
最后，得到两条符合条件的数据。
⼆、select * from A where id in(select id from B)以上查询使⽤了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表数据全部遍历⼀次. 如:A表有10000条记录,B表有1000000条记录,那么最多有可能遍历10000*1000000次,效率很差. 再如:A表有10000条记录,B表有100条记录,那么最多有可能遍历10000*100次,遍历次数⼤⼤减少,效率⼤⼤提升.结论:in()适合B表⽐A表数据⼩的情况exists ⼀、指定⼀个⼦查询，检测⾏的存在。
SQL中EXISTS的用法
SQL中EXISTS的⽤法⽐如在Northwind数据库中有⼀个查询为SELECT c.CustomerId,CompanyName FROM Customers cWHERE EXISTS(SELECT OrderID FROM Orders o WHERE o.CustomerID=c.CustomerID)这⾥⾯的EXISTS是如何运作呢？⼦查询返回的是OrderId字段，可是外⾯的查询要找的是CustomerID和CompanyName字段，这两个字段肯定不在OrderID⾥⾯啊，这是如何匹配的呢？EXISTS⽤于检查⼦查询是否⾄少会返回⼀⾏数据，该⼦查询实际上并不返回任何数据，⽽是返回值True或FalseEXISTS 指定⼀个⼦查询，检测⾏的存在。
语法： EXISTS subquery参数： subquery 是⼀个受限的 SELECT 语句 (不允许有 COMPUTE ⼦句和 INTO 关键字)。
结果类型： Boolean 如果⼦查询包含⾏，则返回 TRUE ，否则返回 FLASE 。
例表A：TableIn例表B：TableEx（⼀）. 在⼦查询中使⽤ NULL 仍然返回结果集select * from TableIn where exists(select null)等同于： select * from TableIn（⼆）. ⽐较使⽤ EXISTS 和 IN 的查询。
注意两个查询返回相同的结果。
select * from TableIn where exists(select BID from TableEx where BNAME=TableIn.ANAME)select * from TableIn where ANAME in(select BNAME from TableEx)（三）. ⽐较使⽤ EXISTS 和 = ANY 的查询。
注意两个查询返回相同的结果。
select * from TableIn where exists(select BID from TableEx where BNAME=TableIn.ANAME)select * from TableIn where ANAME=ANY(select BNAME from TableEx)NOT EXISTS 的作⽤与 EXISTS 正好相反。
Oracle中in与exist,not in与not exist的性能问题
上星期五与ｈaieｒ讨论in跟exists的性能问题，正好想起原来公司的一个关于noｔ in的规定,本想做个实验证明我的观点是正确的，但多次实验结果却给了我一个比较大的教训。
我又咨询了下orａcle公司工作的朋友,确实是我持有的观点太保守了。
于是写个文章总结下，希望对大家有所启发。
后面可能有大篇是关于1００53 ｔraｃe的内容，只作实验证明,可直接忽略看最终的结论即可。
我们知道,in 是把外表和内表作hash 连接,而exｉsｔｓ是对外表作loｏp循环,每次loop循环再对内表进行查询。
一直以来认为exiｓｔｓ比ｉn效率高的说法是不准确的。
如果查询的两个表大小相当,那么用in和exｉsｔs是差别不大的。
但如果两个表中一个较小,一个是大表,则子查询表大的用exｉsts,子查询表小的用ｉn,效率才是最高的。
假定表A（小表）,表B(大表），ｃc列上都有索引:•ｓｅｌｅct ＊ fｒom A ｗhere cc iｎ（select ｃｃｆrom B）； --效率低,用到了A表上cc列的索引•select ＊ froｍ A ｗhｅre exiｓts(seｌeｃt cc from B wheｒｅ cc＝Ａ.cc); --效率高，用到了B 表上cｃ列的索引。
相反的：•select * fｒom B ｗｈｅrｅ cc ｉn （sｅleｃt ｃｃ from A); －-效率高，用到了B表上cc列的索引•ｓelｅct ＊ from B whｅre eｘists(select cｃｆroｍＡ wｈerｅ cc=)； --效率低，用到了A表上cc列的索引通过使用ｅxiｓtｓ,Ｏracle会首先检查主查询，然后运行子查询直到它找到第一个匹配项,这就节省了时间。
Oｒaclｅ在执行IＮ子查询时，首先执行子查询，并将获得的结果列表存放在一个加了索引的临时表中。
在执行子查询之前，系统先将主查询挂起,待子查询执行完毕,存放在临时表中以后再执行主查询。
mysql in语句的替代方法
mysql in语句的替代方法MySQLIn语句常用于查询多个符合条件的记录，但在某些情况下，使用In语句可能会影响查询性能。
因此，了解MySQLIn语句的替代方法对于优化数据库查询非常重要。
一、使用IN关键字替代OR连接多个条件如果需要查询多个字段符合特定值的记录，通常需要使用多个AND连接的条件，这样会增加查询的复杂性。
然而，使用IN关键字可以将多个条件合并成一个查询，提高查询效率。
例如，查询同时符合“年龄大于30”和“性别为男”的记录，可以使用以下查询：```scssSELECT*FROM表名WHERE年龄>30AND性别='男'```使用IN关键字替代，可以简化为：```sqlSELECT*FROM表名WHERE年龄>30AND性别IN('男')```二、使用UNIONALL替代UNIONUNION用于合并两个或多个查询结果集，能够去重并返回唯一的记录。
但是，在某些情况下使用UNION可能会导致查询效率降低。
这时，可以使用UNIONALL来代替，它同样可以实现合并结果集，但是会保留重复记录。
例如，两个相似的查询：查询所有符合条件的记录：```sqlSELECT*FROM表名WHERE条件1```再查询所有符合条件的记录（不包含重复记录）：```sqlSELECT*FROM表名WHERE条件2UNIONSELECT*FROM表名WHERE条件3```使用UNIONALL替代，可以简化为：```sqlSELECT*FROM表名WHERE条件1UNIONALLSELECT*FROM表名WHERE 条件2和条件3（不重复）```三、使用EXISTS替代NOTEXISTS或NOTIN当需要查询子查询是否存在符合条件的记录时，可以使用EXISTS。
它能够返回一个布尔值，表示子查询是否返回任何记录。
如果想要查询不存在符合条件的记录，可以使用NOTEXISTS或NOTIN。
in和not in的用法
in和not in的用法一、引言在数据库操作和编程中，`in` 和 `not in` 是常用的运算符，它们用于测试一个值是否存在于指定的集合中。
本文将详细介绍这两个运算符的用法，并通过示例来展示其应用。
二、`in` 的用法`in` 运算符用于测试一个值是否存在于指定的集合中。
如果值存在于集合中，则结果为真（即 `True`），否则为假（即 `False`）。
1. 数据库操作在数据库操作中，`in` 通常用于筛选符合特定条件的记录。
例如，在查询学生表中成绩在 A 到 C 之间的学生时，可以使用 `in` 运算符：```sqlSELECT * FROM students WHERE grade IN (A, B, C);```这将返回所有成绩在 A 到 C 之间的学生记录。
2. 编程语言在大多数编程语言中，`in` 运算符的使用方式类似。
以 Python 为例，假设我们有一个数字列表，并想检查一个数字是否在该列表中，可以使用 `in` 运算符：```pythonnumbers = [1, 2, 3, 4, 5]if 3 in numbers:print("数字 3 在列表中")else:print("数字 3 不在列表中")```这将输出 "数字 3 在列表中"，因为 3 确实存在于列表中。
`not in` 运算符则用于测试一个值是否不存在于指定的集合中。
如果值不在集合中，则结果为真（即 `True`），否则为假（即 `False`）。
1. 数据库操作在数据库操作中，`not in` 常用于筛选不符合特定条件的记录。
例如，如果我们想查询所有成绩低于 A 的学生，可以使用 `not in`：```sqlSELECT * FROM students WHERE grade NOT IN (A, B, C);```这将返回所有成绩低于 A 的学生记录。
sql语句，实践证明了某种情况下notin的效率高于notexists
sql语句，实践证明了某种情况下notin的效率高于notexists只要百度not in和not exists，清一色的not exists的效率优于not in，毕竟not exists只是去强调是否返回结果集，只是一个bool 值，而not in是返回一个结果集，是由大量大量数据构成的。
所以一开始我在做的时候写的是not in，然后前辈告诉我效率太低，改成了not exists，结果查询速度特别慢。
为什么呢？首先来看看sql语句,本身sql语句特别长，只写出where条件中的not in和not exists筛选部分语句。
not in: where substr(表A.字段A，1，9) not in （select substr(字段B，1，9) from 表B） and 表A.字段C not in （select 字段D from 表C）not exists：where not exists (select 1 from 表 B where substr(表B.字段B，1，9) = substr(表A.字段A，1，9) and not exists （select 1 from 表C where 表C.字段D = 表A.字段C）主表是表A，大概也就不超过10万的数据量吧，然后前面表A先做过一次inner join和两次left join，inner join排除了表A中将近六分之五的数据，两次left join中一次是替换掉表A中的某个字段的值，另一次多取一次值。
这些处理都花极其少的时间。
然后现在这么做远远不够，表A还要根据另外两张表中的数据来进行再次过滤。
这个行为就是通过两个not in来完成的。
一开始借鉴了前辈的提议，用了not exists，毕竟返回一个bool值是大量的节省时间，然后实际结果下来却花了整整3秒多，这对于一个用户来是完完全全不能接受的。
为什么原因呢？最后推导出原因肯定是在前一句not exists中的两个substr。
SQL 里的 EXISTS与in、not exists与not in
SQL 里的EXISTS与in、not exists与not in2011-01-07 10:01:25| 分类：sql | 标签：|字号大中小订阅系统要求进行SQL优化，对效率比较低的SQL进行优化，使其运行效率更高，其中要求对SQL中的部分in/not in修改为exists/not exists修改方法如下：in的SQL语句SELECT id, category_id, htmlfile, title, convert(varchar(20),begintime,112) as pubtimeFROM tab_oa_pub WHERE is_check=1 andcategory_id in (select id from tab_oa_pub_cate where no='1')order by begintime desc修改为exists的SQL语句SELECT id, category_id, htmlfile, title, convert(varchar(20),begintime,112) as pubtimeFROM tab_oa_pub WHERE is_check=1 andexists (select id from tab_oa_pub_cate where tab_oa_pub.category_id=convert(int,no) and no='1') order by begintime desc分析一下exists真的就比in的效率高吗？我们先讨论IN和EXISTS。
select * from t1 where x in ( select y from t2 )事实上可以理解为：select *from t1, ( select distinct y from t2 ) t2where t1.x = t2.y;——如果你有一定的SQL优化经验，从这句很自然的可以想到t2绝对不能是个大表，因为需要对t2进行全表的“唯一排序”，如果t2很大这个排序的性能是不可忍受的。
mysql中=与in区别_浅析mysql中exists与in的区别，空判断
mysql中=与in区别_浅析mysql中exists与in的区别，空判断1、exists的使⽤exists对外表⽤loop逐条查询，每次查询都会查看exists的条件语句，当exists⾥的条件语句能够返回记录⾏时(⽆论记录⾏是的多少，只要能返回)，条件就为真，返回当前loop到的这条记录；反之如果exists⾥的条件语句不能返回记录⾏，则当前loop到的这条记录被丢弃，exists的条件就像⼀个bool条件，当能返回结果集则为true，不能返回结果集则为 false。
如下：select * from user where exists (select 1);对user表的记录逐条取出，由于⼦条件中的select 1永远能返回记录⾏，那么user表的所有记录都将被加⼊结果集，所以与 select * from user;是⼀样的。
如下：select * from user where exists (select * from user where userId = 0);可以知道对user表进⾏loop时，检查条件语句(select * from user where userId = 0)，由于userId永远不为0，所以条件语句永远返回空集，条件永远为false，那么user表的所有记录都将被丢弃。
not exists与exists相反，也就是当exists条件有结果集返回时，loop到的记录将被丢弃，否则将loop到的记录加⼊结果集。
总的来说，如果A表有n条记录，那么exists查询就是将这n条记录逐条取出，然后判断n遍exists条件。
2、in 的使⽤in查询相当于多个or条件的叠加，这个⽐较好理解，⽐如下⾯的查询：select * from user where userId in (1, 2, 3);等效于select * from user where userId = 1 or userId = 2 or userId = 3;not in 与 in相反，如下select * from user where userId not in (1, 2, 3);等效于select * from user where userId != 1 and userId != 2 and userId != 3;总的来说，in查询就是先将⼦查询条件的记录全都查出来，假设结果集为B，共有m条记录，然后再将⼦查询条件的结果集分解成m个，再进⾏m次查询。
oracle in 的用法
EXISTS的执行流程select * from t1 where exists ( select null from t2 where y = x )可以理解为:for x in ( select * from t1 )loopif ( exists ( select null from t2 where y = x.x )thenOUTPUT THE RECORDend ifend loop对于in 和exists的性能区别:如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in,反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists。
其实我们区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询，所以我们会以驱动表的快速返回为目标，那么就会考虑到索引及结果集的关系了另外IN时不对NULL进行处理如：select 1 from dual where null in (0,1,2,null)为空2.NOT IN 与NOT EXISTS:NOT EXISTS的执行流程select .....from rollup Rwhere not exists ( select 'Found' from title Twhere R.source_id = T.Title_ID);可以理解为:for x in ( select * from rollup )loopif ( not exists ( that query ) ) thenOUTPUTend if;end;注意:NOT EXISTS 与NOT IN 不能完全互相替换，看具体的需求。
如果选择的列可以为空，则不能被替换。
例如下面语句，看他们的区别：select x,y from t;x y------ ------1 33 11 21 13 15select * from t where x not in (select y from t t2 )no rowsselect * from t where not exists (select null from t t2where t2.y=t.x )x y------ ------5 NULL所以要具体需求来决定对于not in 和not exists的性能区别：not in 只有当子查询中，select 关键字后的字段有not null约束或者有这种暗示时用not in,另外如果主查询中表大，子查询中的表小但是记录多，则应当使用not in,并使用anti hash join.如果主查询表中记录少，子查询表中记录多，并有索引，可以使用not exists,另外not in 最好也可以用/*+ HASH_AJ */或者外连接+is nullNOT IN 在基于成本的应用中较好比如:select .....from rollup Rwhere not exists ( select 'Found' from title Twhere R.source_id = T.Title_ID);改成（佳）select ......from title T, rollup Rwhere R.source_id = T.Title_id(+)and T.Title_id is null;或者（佳）sql> select /*+ HASH_AJ */ ...from rollup Rwhere ource_id NOT IN ( select ource_idfrom title Twhere ource_id IS NOT NULL )注意：上面只是从理论上提出了一些建议，最好的原则是大家在上面的基础上，能够使用执行计划来分析，得出最佳的语句的写法希望大家提出异议。
SQLServer性能优化之——T-SQLNOTIN和NOTExists
SQLServer性能优化之——T-SQLNOTIN和NOTExists这次介绍⼀下T-SQL中“Not IN” 和“Not Exists”的优化。
Not IN 和 Not Exists 命令 :有些情况下，需要select/update/delete 操作孤⽴数据。
孤⽴数据：不存在主表中⽽存在其关联表中。
操作这样的数据，⼀般第⼀反应是利⽤“Not in” 或 “Not Exists”命令。
使⽤Not IN会严重影响性能，因为这个命令会逐⼀检查每个记录，就会造成资源紧张，尤其是当对⼤数据进⾏更新和删除操作时，可能导致资源被这些操作锁住。
选择NOT IN 还是 NOT Exists现在SQL Server 中有两个命令可以使⽤⼤数据的插⼊、更新、删除操作，性能⽅⾯⽐NOT IN有很⼤的提⾼，语法简单⽐NOT Exists好很多，写出来的语句看上去很清爽。
现在就请它们闪亮登场，Merge 和 Except。
例⼦:⾸先创建两个表use[MyTest]create table Test1 ([id]int, [name]varchar(20))create table Test2 ([id]int, [name]varchar(20), [address]varchar(100))declare@RowC intdeclare@Name varchar(20)set@RowC=0while@RowC<400000Beginset@Name='TestName'+CAST(@RowC as varchar(10))insert into Test1(id, name) values(@RowC, @Name)set@RowC=@RowC+1enddeclare@RowC intdeclare@Name varchar(20)declare@Address varchar(100)set@RowC=0while@RowC<500000Beginset@Name='TestName'+CAST(@RowC as varchar(10))set@Address='TestAddress'+CAST(@RowC as varchar(10))insert into Test2([id], [name], [address]) values(@RowC, @Name, @Address)set@RowC=@RowC+1endSELECT[name]FROM Test2 where[name]not in (select[name]from Test1)UPDATE Test2 SET[name]=N'New_Name'where[name]not in (select[name]from Test1)DELETE Test2 FROM Test2 where[name]not in (select[name]from Test1)merge Test2 T using (select name from Test2 except select name from Test1 )S on =when matched then update SET name=N'New_Name' ;merge Test2 T using (select name from Test2 except select name from Test1 )S on =when matched then delete ;SELECT*FROM Test2 S where not exists (select1from Test2 inner join Test1 on = and =)注意，上⾯还是有⼀部分使⽤了Not Exists：SELECT name FROM Test2 S where not exists (select1from Test2 inner join Test1 on = and =)现在需要使⽤简洁的Except：select name from Test1 except select name from Test2。
Oracle中in与exist,notin与notexist性能问题
上星期五与haier讨论in跟exists的性能问题，正好想起原来公司的一个关于not in的规定，本想做个实验证明我的观点是正确的，但多次实验结果却给了我一个比较大的教训。
我又咨询了下oracle公司工作的朋友，确实是我持有的观点太保守了。
于是写个文章总结下，希望对大家有所启发。
后面可能有大篇是关于10053 trace的内容，只作实验证明，可直接忽略看最终的结论即可。
我们知道，in 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。
一直以来认为exists比in效率高的说法是不准确的。
如果查询的两个表大小相当，那么用in和exists是差别不大的。
但如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in，效率才是最高的。
假定表A（小表），表B（大表），cc列上都有索引：•select * from A where cc in (select cc from B)。
--效率低，用到了A表上cc列的索引•select * from A where exists(select cc from B where 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=)。
--效率低，用到了A表上cc 列的索引通过使用exists，Oracle会首先检查主查询，然后运行子查询直到它找到第一个匹配项，这就节省了时间。
Oracle在执行IN子查询时，首先执行子查询，并将获得的结果列表存放在一个加了索引的临时表中。
在执行子查询之前，系统先将主查询挂起，待子查询执行完毕，存放在临时表中以后再执行主查询。
SQLSERVER中关于exists和in的简单分析
SQLSERVER中关于exists和in的简单分析In与Exists这两个函数是差不多的，但由于优化⽅案不同，通常NOT Exists要⽐NOT IN要快，因为NOT EXISTS可以使⽤结合算法⼆NOT IN就不⾏了，⽽EXISTS则不如IN快，因为这时候IN可能更多的使⽤结合算法。
如图，现在有两个数据集，左边表⽰#tempTable1，右边表⽰#tempTable2。
现在有以下问题：1.求两个集的交集？2.求tempTable1中不属于集#tempTable2的集？先创建两张临时表：create table #tempTable1(argument1 nvarchar(50),argument2 varchar(20),argument3 datetime,argument4 int);insert into #tempTable1(argument1,argument2,argument3,argument4)values('preacher001','130********',GETDATE()-1,1);insert into #tempTable1(argument1,argument2,argument3,argument4)values('preacher002','23218757',GETDATE()-2,2);insert into #tempTable1(argument1,argument2,argument3,argument4)values('preacher003','13018757',GETDATE()-3,3);insert into #tempTable1(argument1,argument2,argument3,argument4)values('preacher004','13023257',GETDATE()-4,4);insert into #tempTable1(argument1,argument2,argument3,argument4)values('preacher005','13023218',GETDATE()-5,5);insert into #tempTable1(argument1,argument2,argument3,argument4)values('preacher006','13023218',GETDATE()-6,6);insert into #tempTable1(argument1,argument2,argument3,argument4)values('preacher007','13023218',GETDATE()-7,7);insert into #tempTable1(argument1,argument2,argument3,argument4)values('preacher008','13023218',GETDATE()-8,8);create table #tempTable2(argument1 nvarchar(50),argument2 varchar(20),argument3 datetime,argument4 int);insert into #tempTable2(argument1,argument2,argument3,argument4)values('preacher001','130********',GETDATE()-1,1);insert into #tempTable2(argument1,argument2,argument3,argument4)values('preacher0010','23218757',GETDATE()-10,10);insert into #tempTable2(argument1,argument2,argument3,argument4)values('preacher003','13018757',GETDATE()-3,3);insert into #tempTable2(argument1,argument2,argument3,argument4)values('preacher004','13023257',GETDATE()-4,4);insert into #tempTable2(argument1,argument2,argument3,argument4)values('preacher009','13023218',GETDATE()-9,9);⽐如，我现在以#tempTable1和#tempTable2的argument1作为参照1.求两集的交集：1）in ⽅式select * from #tempTable2 where argument1 in(select argument1 from #tempTable1)2）exists ⽅式select * from #tempTable2 t2 where exists(select * from #tempTable1 t1 where t1.argument1=t2.argument1)2.求tempTable1中不属于集#tempTable2的集1）in ⽅式select * from #tempTable1 where argument1 not in(select argument1 from #tempTable2)2）exists ⽅式select * from #tempTable1 t1 where not exists(select * from #tempTable2 t2 where t1.argument1=t2.argument1)。
sql中exists,notexists的用法
sql中exists,notexists的⽤法exists : 强调的是是否返回结果集，不要求知道返回什么, ⽐如：select name from student where sex = 'm' and mark exists(select 1 from grade where ...) ,只要exists引导的⼦句有结果集返回，那么exists这个条件就算成⽴了,⼤家注意返回的字段始终为1，如果改成“select 2 from grade where ...”，那么返回的字段就是2，这个数字没有意义。
所以exists⼦句不在乎返回什么，⽽是在乎是不是有结果集返回。
⽽ exists 与 in 最⼤的区别在于 in引导的⼦句只能返回⼀个字段，⽐如：select name from student where sex = 'm' and mark in (select 1,2,3 from grade where ...)，in⼦句返回了三个字段，这是不正确的，exists⼦句是允许的，但in只允许有⼀个字段返回，在1，2，3中随便去了两个字段即可。
⽽not exists 和not in 分别是exists 和 in 的对⽴⾯。
exists （sql 返回结果集为真） not exists (sql 不返回结果集为真）下⾯详细描述not exists的过程：如下：表AID NAME 1 A12 A23 A3表BID AID NAME1 1 B12 2 B2 3 2 B3表A和表B是１对多的关系 A.ID => B.AIDSELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHEREA.ID=B.AID)执⾏结果为1 A12 A2原因可以按照如下分析SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=１)--->SELECT * FROM B WHERE B.AID=１有值返回真所以有数据SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=2)--->SELECT * FROM B WHERE B.AID=２有值返回真所以有数据SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=3)--->SELECT * FROM B WHERE B.AID=３⽆值返回真所以没有数据NOT EXISTS 就是反过来SELECT ID,NAME FROM A WHERE NOT EXIST (SELECT * FROM B WHERE A.ID=B.AID)执⾏结果为3A3===========================================================================EXISTS = IN,意思相同不过语法上有点点区别，好像使⽤IN效率要差点，应该是不会执⾏索引的原因SELECTID,NAME FROM A WHERE ID IN (SELECT AID FROM B)NOT EXISTS = NOT IN ,意思相同不过语法上有点点区别SELECT ID,NAME FROM A WHERE ID NOT IN (SELECT AID FROM B)有时候我们会遇到要选出某⼀列不重复,某⼀列作为选择条件,其他列正常输出的情况.如下⾯的表table:Id Name Class Count Date1 苹果⽔果 10 2011-7-11 桔⼦⽔果 20 2011-7-21 ⾹蕉⽔果 15 2011-7-32 ⽩菜蔬菜 12 2011-7-12 青菜蔬菜 19 2011-7-2如果想要得到下⾯的结果:(Id唯⼀,Date选最近的⼀次)1 ⾹蕉⽔果 15 2011-7-32 青菜蔬菜 19 2011-7-2正确的SQL语句是:SELECT Id, Name, Class, Count, DateFROM table tWHERE (NOT EXISTS(SELECT Id, Name, Class, Count, Date FROM tableWHERE Id = t.Id AND Date > t.Date))如果⽤distinct,得不到这个结果, 因为distinct是作⽤与所有列的SELECT DISTINCT Id, Name, Class, Count, Date FROM table结果是表table的所有不同列都显⽰出来,如下所⽰:1 苹果⽔果 10 2011-7-11 桔⼦⽔果 20 2011-7-21 ⾹蕉⽔果 15 2011-7-32 ⽩菜蔬菜 12 2011-7-12 青菜蔬菜 19 2011-7-2如果⽤Group by也得不到需要的结果,因为Group by 要和聚合函数共同使⽤,所以对于Name,Class和Count列要么使⽤Group by,要么使⽤聚合函数. 如果写成SELECT Id, Name, Class, Count, MAX(Date)FROM tableGROUP BY Id, Name, Class, Count得到的结果是1 苹果⽔果 10 2011-7-11 桔⼦⽔果 20 2011-7-21 ⾹蕉⽔果 15 2011-7-32 ⽩菜蔬菜 12 2011-7-12 青菜蔬菜 19 2011-7-2如果写成SELECT Id, MAX(Name), MAX(Class), MAX(Count), MAX(Date)FROM tableGROUP BY Id得到的结果是:1 ⾹蕉⽔果 20 2011-7-32 青菜蔬菜 19 2011-7-2如果⽤in有时候也得不到结果,（有的时候可以得到，如果Date都不相同（没有重复数据），或者是下⾯得到的Max（Date）只有⼀个值）SELECT DISTINCT Id, Name, Class, Count, Date FROM tableWHERE (Date IN(SELECT MAX(Date)FROM tableGROUP BY Id))得到的结果是：（因为MAX(Date)有两个值2011-7-2，2011-7-3）1 桔⼦⽔果 20 2011-7-21 ⾹蕉⽔果 15 2011-7-32 青菜蔬菜 19 2011-7-2注意in只允许有⼀个字段返回有⼀种⽅法可以实现：SELECT Id, Name, Class, COUNT, DateFROM table1 tWHERE (Date = (SELECT MAX(Date) FROM table1 WHERE Id = t .Id))⽐如在Northwind数据库中有⼀个查询为SELECT c.CustomerId,CompanyName FROM Customers cWHERE EXISTS(SELECT OrderID FROM Orders o WHERE o.CustomerID=c.CustomerID) 这⾥⾯的EXISTS是如何运作呢？⼦查询返回的是OrderId字段，可是外⾯的查询要找的是CustomerID和CompanyName字段，这两个字段肯定不在OrderID⾥⾯啊，这是如何匹配的呢？ EXISTS⽤于检查⼦查询是否⾄少会返回⼀⾏数据，该⼦查询实际上并不返回任何数据，⽽是返回值True或FalseEXISTS 指定⼀个⼦查询，检测⾏的存在。
sql语句中in与exist not in与not exist 的区别
sql语句中in与exist not in与not exist 的区别in和existsin 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop 循环再对内表进行查询。
一直以来认为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=)效率高，用到了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=)效率低，用到了A表上cc列的索引。
not in 和not exists如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。
所以无论那个表大，用not exists都比not in要快。
in 与=的区别select name from student where name in ('zhang','wang','li','zhao');与select name from student where name='zhang' or name='li' or name='wang' or name='zhao'的结果是相同的。
关于sql语句in的使用注意规则
关于sql语句in的使⽤注意规则想必⼤家都⽤过sql中的in语句吧，我这⾥描述下我遇到的⼀种in语句问题，并总结⼀些给⼤家分享下，不对的地⽅还希望⼤虾指点下。
问题描述：IN⼦查询时，⼦查询中字段在表中不存在时语句却不报错平常⼯作中我们会经常⽤到in或者not in语句来处理⼀些问题，⽐如通过in⼦查询语句检索符合或者不符合条件的集合结果、批量删除、修改⼀些符合条件或者不符合条件的集合。
但⼤家是否注意到当⼦查询中字段名在表中不存在时语句不会报错（会返⽗查询中所有的结果），如果⼤家不注意这点，在使⽤in语句进⾏批量删除时就可能悲剧了。
下⾯⽤实例来说明1--⼀个简单的in查询语句2select*from tuser where userno in(select userno from filter_barcode)上⾯这条语句⼦查询⾥，userno 并不存在filter_barcode表中，但是整个语句确能正常执⾏（执⾏⼦查询的话会报字段不存在的提⽰），⽽且返回的是tuser表中所有的结果集。
如果⼤家不注意这种情况，⼀旦不是⽤来查询，是⽤来删除的，那整个表数据就被不知不觉给删除了。
但是当将⼦查询中userno字段改成⼀个即不再tuser也不再filter_barcode表中的字段，那语句就会报错select * from tuser where userno in(select useno from filter_barcode)Msg 207, Level16, State 1, Line 1列名'useno'⽆效。
原因：原来是在不使⽤表别名的前提下如果in⼦查询⾥字段在内表找不到就会去引⽤外表的。
现实情况下⼦查询引⽤外层查询的列是正常的，只不过⼀般不在in⼦查询中引⽤外层查询的列。
但是在exists,not exists⼦查询中⽤得⽐较多，select a.*from tuser a where exists(select top1*from filter_barcode b where erno=erno)--执⾏上⾯这语句就会提⽰Msg 207, Level16, State 1, Line 1列名'userno'⽆效。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
1 3
正如所看到的，not in 出现了不期望的结果集，存在逻辑错误。如果看一下上述两个select语句的执行计划，也会不同。后者使用了hash_aj。
因此，请尽量不要使用not in(它会调用子查询)，而尽量使用not exists(它会调用关联子查询)。如果子查询中返回的任意一条记录含有空值，则查询将不返回任何记录，正如上面例子所示。
效率高，用到了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=)
效率低，用到了A表上cc列的索引。
sql语句中in与exist not in与not exist 的区别
2010年03月23日 星期二 下午 02:20
in和exists
in 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。一直以来认为exists比in效率高的说法是不准确的。
insert into t2 values (1,null);
select * from t1 where c2 not in (select c2 from t2);
no rows found
select * from t1 where not exists (select 1 from t2 where t1.c2=t2.c2);
与
select name from student where name='zhang' or name='li' or name='wang' or name='zhao'
的结果是相同的。
not in 逻辑上不完全等同于not exists，如果你误用了not in，小心你的程序存在致命的BUG：
请看下面的例子：
create table t1 (c1 number,c2 number);
create table t2 (c1 number,c2 number);
insert into t1 values (1,2);
insert into t1 values (1,3);
insert into t2 values (1,2);
not in 和not exists如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。
in 与 =的区别
select name from student where name in ('zhang','wang','li','zhao');
如果两个表中一个较小，一个是大表，则子查询表大的用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=)
除非子查询字段有非空限制，这时可以使用not in ,并且也可以通过提示让它使用hasg_aj或mergin和exists差别不大。
全文：
in和exists
in 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。一直以来认为exists比in效率高的说法是不准确的。
如果查询的两个表大小相当，那么用in和exists差别不大。