exists 和not exists 用法简介及与IN和not In的区别

使用 EXISTS 和 NOT EXISTS 引入的子查询可用于两种集合原理的操作：交集与差集。

两个集合的交集包含同时属于两个原集合的所有元素。差集包含只属于两个集合中的第一个集合的元素。

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

NOT EXISTS:指定一个子查询，检查行的不存在

exists (sql 返回结果集为真)
not exists (sql 不返回结果集为真=no rows)

 

例一：

表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 1-->n 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

 

例二：

查找由位于以字母 B 开头的城市中的任一出版商出版的书名：

SELECT DISTINCT pub_name FROM publishers WHERE EXISTS (SELECT * FROM titles WHERE pub_id = publishers.pub_id AND type ='business')ORSELECT distinct pub_name FROM publishers WHERE pub_id IN (SELECT pub_id FROM titles WHERE type = 'business')

 

要查找不出版商业书籍的出版商的名称：

SELECT pub_name FROM publishers WHERE NOT EXISTS (SELECT * FROM titles WHERE pub_id = publishers.pub_id AND type ='business')

查找已经不销售的书的名称:

SELECT title FROM titles WHERE NOT EXISTS (SELECT title_id FROM sales WHERE title_id = titles.title_id)

 

项目案例：

下面是最近项目的经验总结：

如要从客户资金表（Cfunds ）中获取每个用户最新的一条资金情况，则在这个时候not exists 显得尤为有用，可以省掉你去写一大堆子查询、Group By、In的麻烦

这个是表：

Select * From Cfunds aWhere Not exists(Select * From Cfunds Where CAccount=a.CAccount And Counttime>a.CountTime)order by CountTime Desc

 

 

==========================================================================

IN:确定给定的值是否与子查询或列表中的值相匹配。

IN 关键字使您得以选择与列表中的任意一个值匹配的行。

当要获得居住在 California、Indiana 或 Maryland 州的所有作者的姓名和州的列表时，就需要下列查询：

SELECT ProductID, ProductName FROM Northwind.dbo.Products WHERE CategoryID = 1 OR CategoryID = 4 OR CategoryID = 5

然而，如果使用 IN，少键入一些字符也可以得到同样的结果：

SELECT ProductID, ProductName FROM Northwind.dbo.Products WHERE CategoryID IN (1, 4, 5)

IN 关键字之后的项目必须用逗号隔开，并且括在括号中。

下列查询在 titleauthor 表中查找在任一种书中得到的版税少于 50% 的所有作者的 au_id，然后从 authors 表中选择 au_id 与

titleauthor 查询结果匹配的所有作者的姓名：

SELECT au_lname, au_fname FROM authors WHERE au_id IN (SELECT au_id FROM titleauthor WHERE royaltyper < 50)

结果显示有一些作者属于少于 50% 的一类。

NOT IN:通过 NOT IN 关键字引入的子查询也返回一列零值或更多值。

以下查询查找没有出版过商业书籍的出版商的名称。

SELECT pub_name FROM publishers WHERE pub_id NOT IN (SELECT pub_id FROM titles WHERE type = 'business')

 

================================================================================

两者写法的区别：

EXISTS:后面可以是整句的查询语句如:SELECT * FROM titles

IN:后面只能是对单列:SELECT pub_id FROM titles

================================================================================

以上是转载的

 

个人总结：

IN:

keyword in (A,B,C) 可以转化为 keyword = A or keyword =B or keyword =C

NOT IN:

keyword not in (A,B,C) 可以转化为 keyword <> A and keyword <> B and keyword <> C

如果其中 A,B,C 中有为NULL值的则 Keyword not in (A,B,C) 该表达式返回值为FALSE，结果就是no rows

EXISTS:

这个可以想成是父查询的对于某个投影的结果集与子查询对于所有投影或者部分投影的交集。这个结果集同属于父子查询的结果集

比如: select ename from emp e where exists ( select  * from dept where deptno = e.deptno )

emp关系表与dept关系表是 n:1 关系--> E-R 图转换关系模型规则 在emp上建立dept的主键属性也就是deptno,这样两者就可以关联起来了

可以这么理解查询的就是emp员工表是不是都有所属部门了，是否还有员工没有所属部门

(所有员工部门号的集合)与(所有部门号的集合) 的交集, 交集关系的实现就是通过把员工表中员工所在部门号拿来与部门表中所有部门号比较查看是否全部都有。

emp:  empno, ename,deptno

dept:  deptno,dname

关于null: 即使子集集合返回的行是null，但它仍然是返回null了所以还是认为存在的

exists(只要不是no rows) = true

exists(no rows) = false

SQL> select 'true' from dual where exists( select null from dual);'TRU----true1 row selected.

感觉exists对于空的处理与in 都一样

select * from emp where deptno in ( select deptno from dept);

select * from emp  e where exists(select * from dept where deptno = e.deptno);

以上如果

NOT EXISTS:

这个是父查询与子查询的差集关系

可以理解为（父查询的部分投影的结果集）不包含（子查询全部投影结果集）的结果集，这个最终结果集是属于父查询结果集的子集。

下面的例子就是检索的父查询部门的部门编号中不包含员工的结果集

举个例子：select * from dept d where not exists( select * from emp e where e.deptno = d.deptno);

代表的意思就是 dept部门中的所有部门编号的集合减去员工表中存在相同部门编号，得出剩下的集合就是，

部门中没有员工的结合结果

not exists(n>0 rows) = false

not exists(no rows)    = true

如果d.deptno = null 那么 整个子查询返回的就是no rows,not exists(no rows) = true 有结果集返回

换成not in

   select * from dept d where d.deptno not in ( select deptno from emp ) e;

   如果子查询deptno有null,那么由于not in 可以转化为 d.deptno <> e.deptno_1 and d.deptno <>e.deptno_2

   如果e.deptno_2是null那么这整个where 后面的结果都变为null了 所以会存在没有返回值的情况