SQL EXISTS

一直对exists的用法不清楚，本次学习exists,并作出相应学习总结。

 

1.创造测试环境
SYS@ora122>create table a(id int,name varchar2(200));
SYS@ora122>insert into a values(1,'a');
SYS@ora122>insert into a values(2,'b');
SYS@ora122>insert into a values(2,'c');
SYS@ora122>insert into a values(3,'a');
SYS@ora122>commit;

2.简单exists 举例说明，对于单个exists ,子查询返回null,则结果为null,子查询非null,则查询显示输出结果
SYS@ora122> select * from a where exists(select id from a where name='a')
  ID NAME
---- --------------------
   1 a
   2 b
   2 c
   3 a

SYS@ora122> select * from a where exists(select id from a where name='d');

no rows selected

SYS@ora122>select * from a where not exists(select id from a where name='a');
no rows selected

 

3.创建测试索引，观察执行计划，找到执行计划的规律
SYS@ora122>create index a_id on a(id);
SYS@ora122>create index a_name on a(name);
SYS@ora122>set autotrace on
SYS@ora122>select id from a where not exists(select id from a where name='a');
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3514764880
-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |     4 |    52 |     4   (0)| 00:00:01 |
|*  1 |  FILTER            |        |       |       |            |          |
|   2 |   TABLE ACCESS FULL| A      |     4 |    52 |     3   (0)| 00:00:01 |
|*  3 |   INDEX RANGE SCAN | A_NAME |     2 |   204 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter( NOT EXISTS (SELECT 0 FROM "A" "A" WHERE "NAME"='a'))
   3 - access("NAME"='a')
可以得知，表A全表扫、not exists 查询结果为2行记录， 最终filter 返回null值


SQL改写

select id from a where id>2 and not exists(select id from a where name='a');
no rows selected

----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
|* 2 | INDEX RANGE SCAN| A_ID | 1 | 13 | 1 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN| A_NAME | 2 | 204 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter( NOT EXISTS (SELECT 0 FROM "A" "A" WHERE "NAME"='a'))
2 - access("ID">2)
3 - access("NAME"='a')         执行计划并未改变多少

 

SYS@ora122>SELECT 0 FROM "A" "A" WHERE "NAME"='a';

0
----------
0
0

SYS@ora122>select id from a where name='a';

ID
----
1
3

 

 

SQL改写

SYS@ora122>select * from a where name='a' and not exists(select id from a where id=8);

ID NAME
---- --------------------
1 a
3 a

-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 230 | 3 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| A | 2 | 230 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | A_NAME | 2 | | 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | A_ID | 1 | 13 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter( NOT EXISTS (SELECT 0 FROM "A" "A" WHERE "ID"=8))
3 - access("NAME"='a')
4 - access("ID"=8)

 

 

与IN不同的是，

in 相互对等匹配返回结果

exists 要么都可以，要么都失败

not exists 与exists本质相同

not in ，则是第一个查询结果与not in结果匹配进行过滤