oracle数据库下关键字EXIST，NOT EXISTS使用讲解

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是1对多的关系 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=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