oracle中exists和not exists用法举例详解_Oracle

exists （sql 返回结果集为真）
not exists (sql 不返回结果集为真）
exists 与 in 意思相同，语法不同，效率高于in
not exists 与 not in 意思相同，语法不同，效率高于in

基本概念：

select * from a where not exists(select * from b where a.id = b.id);
select * from a where exists(select * from b where a.id = b.id);

1、首先执行外查询select * from a，然后从外查询的数据取出一条数据传给内查询。

2、内查询执行select * from b，外查询传入的数据和内查询获得的数据根据where后面的条件做匹对，如果存在数据满足a.id=b.id则返回true，如果一条都不满足则返回false。

3、内查询返回true，则外查询的这行数据保留，反之内查询返回false，则外查询的这行数据不显示。外查询的所有数据逐行查询匹对。

注意：exists或not exists的执行顺序是先执行外查询再执行内查询。这和我们学的子查询概念冲突。

举例

如下：

表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 exist (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=１) 
--->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 a

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

exists = in,意思相同不过语法上有点点区别，好像使用in效率要差点，应该是不会执行索引的原因 
select id,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)

下面是普通的用法：

sql中in,not in,exists,not exists的用法和差别:

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 whereroyaltyper <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 和 not exists 引入的子查询可用于两种集合原理的操作：交集与差集。

　　两个集合的交集包含同时属于两个原集合的所有元素。

　　差集包含只属于两个集合中的第一个集合的元素。　　

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

　　本示例所示查询查找由位于以字母 b 开头的城市中的任一出版商出版的书名：

select distinct pub_name from publishers where exists (select * from titles where pub_id= publishers.pub_id and type = 'business') 
select distinct pub_name from publishers where pub_id in (select pub_id from titles wheretype = 'business')

两者的区别:

　　exists:后面可以是整句的查询语句如:select * from titles

　　in:后面只能是对单列:select pub_id from titles

　　not exists:

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

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)

语法

exists subquery参数 subquery:是一个受限的 select 语句 (不允许有 compute 子句和 into 关键字)。有关更多信息，请参见select 中有关子查询的讨论。

结果类型:boolean

结果值:如果子查询包含行，则返回 true。

示例

a. 在子查询中使用 null 仍然返回结果集这个例子在子查询中指定 null，并返回结果集，通过使用 exists 仍取值为 true。

use northwind 
go 
select categoryname 
from categories 
where exists (select null) 
order by categoryname asc 
go

b. 比较使用 exists 和 in 的查询

这个例子比较了两个语义类似的查询。第一个查询使用 exists 而第二个查询使用 in。注意两个查询返回相同的信息。

use pubs 
go 
select distinct pub_name 
from publishers 
where exists 
    (select * 
    from titles 
    where pub_id = publishers.pub_id 
    and type = \'business\') 
go 

-- or, using the in clause: 

use pubs 
go 
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

c.比较使用 exists 和 = any 的查询

本示例显示查找与出版商住在同一城市中的作者的两种查询方法：第一种方法使用 = any，第二种方法使用exists。注意这两种方法返回相同的信息。

use pubs 
go 
select au_lname, au_fname 
from authors 
where exists 
    (select * 
    from publishers 
    where authors.city = publishers.city) 
go 

-- or, using = any 

use pubs 
go 
select au_lname, au_fname 
from authors 
where city = any 
    (select city 
    from publishers) 
go

d.比较使用 exists 和 in 的查询

本示例所示查询查找由位于以字母 b 开头的城市中的任一出版商出版的书名：

use pubs 
go 
select title 
from titles 
where exists 
    (select * 
    from publishers 
    where pub_id = titles.pub_id 
    and city like \'b%\') 
go 

-- or, using in: 

use pubs 
go 
select title 
from titles 
where pub_id in 
    (select pub_id 
    from publishers 
    where city like \'b%\') 
go

e. 使用 not exists

not exists 的作用与 exists 正相反。如果子查询没有返回行，则满足 not exists 中的 where 子句。本示例查找不出版商业书籍的出版商的名称：

use pubs 
go 
select pub_name 
from publishers 
where not exists 
    (select * 
    from titles 
    where pub_id = publishers.pub_id 
    and type = \'business\') 
order by pub_name 
go