IN 和NOT IN，EXISTS 和NOT EXISTS

Not In的用法/特点：

在SQLServer中，子查询可以分为相关子查询和无关子查询，对于无关子查询来说，Not In子句比较常见，但Not In潜在会带来下面两种问题：

• 结果不准确

• 查询性能低下

因此，不建议使用Not In

1. 在SQL Server中，Null值并不是一个值，而是表示特定含义，其所表示的含义是“Unknow”，可以理解为未定义或者未知，因此任何与Null值进行比对的二元操作符结果一定为Null，包括Null值本身。而在SQLServer中，Null值的含义转换为Boolean类型的结果为False。

2. Null值与任何值进行对比结果都为Null

3. Not In产生不准确的值

条件3不属于Not In后面列表的任意一个，该查询却不返回任何值，与预期的结果不同，那么具体原因就是Not In子句对于Null值的处理，在SQLServer中，语句1的Not In子句其实可以等价转换为如图所示的语句二。

在上图中可以看到Not In可以转换为条件对于每个值进行不等比对，并用逻辑与连接起来，而前面提到过Null值与任意其他值做比较时，结果永远为Null，在Where条件中也就是False，因此3<>null就会导致不返回任何行，导致Not In子句产生的结果在意料之外。

因此，Not In子句如果来自于某个表或者列表很长，其中大量值中即使存在一个Null值，也会导致最终结果不会返回任何数据。

解决办法？

解决办法就是不使用Not In，而使用Not Exists作为替代。Exists的操作符不会返回Null，只会根据子查询中的每一行决定返回True或者False，当遇到Null值时，只会返回False，而不会由某个Null值导致整个子查询表达式为Null。对于上图中所示的查询，我们可以改写为子查询，如下图所示。

想要彻底解决，我们不得不看一下IN、NOT IN、EXISTS、NOT EXISTS的区别：

exists : 强调的是是否返回结果集，不要求知道返回什么。

比如：

select name form studentwhere sex = ‘m’ and mark exists (select 1 from grade where…)

只要exists引导的子句有结果集返回，那么exists这个条件就算成立了,大家注意返回的字段始终为1，如果改成“select 2 from gradewhere ...”，那么返回的字段就是2，这个数字没有意义。所以exists子句不在乎返回什么，而是在乎是不是有结果集返回。

而exists 与 in 最大的区别在于 in引导的子句只能返回一个字段。

比如

select name form studentwhere sex = ‘m’ and mark in (select 1，2，3 from grade where…)

in子句返回了三个字段，这是不正确的，但是exists子句是允许的，但in只允许有一个字段返回，在1，2，3中随便去了两个字段即可。

而notexists 和notin 分别是exists和 in 的 对立面。

exists主要看exists括号中的sql语句结果是否有结果，有结果：才会继续执行where条件；没结果：视为where条件不成立。

exists (sql 返回结果集，为真）

主要看not exists括号中的sql语句是否有结果，无结果：才会继续执行where条件；有结果：视为where条件不成立。

not exists (sql 不返回结果集，为真）

not exists：经过测试，当子查询和主查询有关联条件时，相当于从主查询中去掉子查询的数据。

例如：

test数据：id name

1 张三

2 李四

select* from test c where not exists

(select1 from test t where t.id= '1' )

--无结果

select* from test c where not exists

(select1 from test t where t.id= '1' and t.id = c.id)

--返回2 李四