SQL中INEXISTS和IN 的区别和联系

2022-03-18 07:12:48

SET NOCOUNT ON ， SET NOCOUNT OFF
当 SET NOCOUNT 为 ON 时，不返回计数（表示受 Transact-SQL 语句影响的行数）。
当 SET NOCOUNT 为 OFF 时，返回计数。
如果存储过程中包含的一些语句并不返回许多实际的数据，则该设置由于大量减
少了网络流量，因此可显著提高性能。

SQL 中 IN 和 EXISTS 用法的区别：

NOT IN

SELECT DISTINCT MD001 FROM BOMMD WHERE MD001 NOT IN (SELECT MC001

FROM BOMMC)

NOT EXISTS,exists 的用法跟 in 不一样，一般都需要和子表进行关联，而且关联时，需要
用索引，这样就可以加快速度

select DISTINCT MD001 from BOMMD WHERE NOT EXISTS (SELECT MC001 FROM

BOMMC where BOMMC.MC001 = BOMMD.MD001 )

exists 是用来判断是否存在的，当 exists( 查询 )中的查询存在结果时则返回真，否则返回假。
not exists 则相反。
exists 做为 where 条件时，是先对 where 前的主查询询进行查询，然后用主查询的结果
一个一个的代入 exists 的查询进行判断，如果为真则输出当前这一条主查询的结果，否则
不输出。
in 和 exists
in 是把外表和内表作 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=A.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=B.cc)

效率低，用到了 A 表上 cc 列的索引。

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

SQL中 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'

的结果是相同的。

例子如下（即 exists 返回 where 后 2 个比较的 where 子句中相同值， not exists 则返回 where 子句中不同值）：
exists (sql 返回结果集为真 )
not exists (sql 不返回结果集为真 )

如下：
表 A
ID NAME
1 A1
2 A2
3 A3

表 B
ID AID NAME
1 1 B1
2 2 B2
3 2 B3

表 A 和表 B 是一对多的关系 A.ID --> B.AID

SELECT ID , NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE A.ID =B.AID)

执行结果为
1 A1
2 A2

原因可以按照如下分析
SELECT ID , NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID = 1)
-->SELECT * FROM B WHERE B.AID = 1 有值返回真所以有数据
SELECT ID , NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID = 2)
-->SELECT * FROM B WHERE B.AID = 2 有值返回真所以有数据
SELECT ID , NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID = 3)
-->SELECT * FROM B WHERE B.AID = 3 无值返回真所以没有数据
NOT EXISTS 就是反过来
SELECT ID , NAME FROM A WHERE NOT EXIST ( SELECT * FROM B WHERE A.ID =B.AID)
执行结果为
3 A3

SQL 中 in与 exists区别：

IN
确定给定的值是否与子查询或列表中的值相匹配。
EXISTS
指定一个子查询，检测行的存在。
比较使用 EXISTS 和 IN 的查询
这个例子比较了两个语义类似的查询。第一个查询使用 EXISTS 而第二个查询使用 IN 。注
意两个查询返回相同的信息。
USE pubs

SELECT DISTINCT pub_name

FROM publishers

WHERE EXISTS

(SELECT *

FROM titles

WHERE pub_id = publishers.pub_id

AND type = 'business')

using the IN clause:
USE pubs；

SELECT distinct pub_name

FROM publishers

WHERE pub_id IN

(SELECT pub_id

FROM titles

WHERE type = 'business')

GO
下面是任一查询的结果集：
pub_name
----------------------------------------
Algodata Infosystems
New Moon Books
(2 row(s) affected)

exits 相当于存在量词：表示集合存在 ,也就是集合不为空只作用一个集合 .
例如：
exist P 表示 P 不空时为真 ; not exist P 表示 p 为空时为真

in 表示一个标量和一元关系的关系。
例如：
s in P 表示当 s 与 P 中的某个值相等时为真 ; s not in P 表示 s 与 P 中的每一个值都不相等时为真

in 和 exists性能比较：

in 是把外表和内表作 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=A.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=B.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'
的结果是相同的。

生产环境真实比较：

150节点,数据量 :5000w-3000w:

性能：exists>join null>in

码农公寓

相关文章