In和exists使用及性能分析(二)：exists的使用
本节主要讨论exists的使用。
二、exists的使用
2.1 exists的使用
2.1.1内、外两表关联字段都非空
SQL> select * from tb1;
T1 NAME1
---------- ----------
1 1
2 2
SQL> select * from tb2;
T2 NAME2
---------- ----------
2 2
3 3
SQL> select count(1) from tb1 where exists (select 1 from tb2 where tb1.t1=tb2.t2);
COUNT(1)
----------
1
SQL> select count(1) from tb1 where not exists (select 1 from tb2 where tb1.t1=tb2.t2);
COUNT(1)
----------
1
结果与一般思维相符，没什么好说的。
2.1.2 当外表关联字段无空值、内表关联列表有空值时
SQL> select * from tb1;
T1 NAME1
---------- ----------
1 1
2 2
SQL> select * from tb2;
T2 NAME2
---------- ----------
2
2
3 3
SQL> select count(1) from tb1 where exists (select 1 from tb2 where tb1.t1=tb2.t2);
--T1=2符合条件，返回结果与预期相符
COUNT(1)
----------
1
SQL> select count(1) from tb1 where not exists (select 1 from tb2 where tb1.t1=tb2.t2);
--T1=1符合条件，返回结果与预期相符
COUNT(1)
----------
1
2.1.3当外表关联字段有空值、内表关联列表无空值时
SQL> select * from tb1;
T1 NAME1
---------- ----------
1 1
2 2
3
SQL> select * from tb2;
T2 NAME2
---------- ----------
2 2
SQL> select count(1) from tb1 where exists (select 1 from tb2 where tb1.t1=tb2.t2);
--符合常规逻辑
COUNT(1)
----------
1
SQL> select count(1) from tb1 where not exists (select 1 from tb2 where tb1.t1=tb2.t2);
--符合常规逻辑，注意与in的区别
COUNT(1)
----------
2
2.1.4当内、外表的关联字段都有空值时
SQL> select * from tb1;
T1 NAME1
---------- ----------
1 1
2 2
3
SQL> select * from tb2;
T2 NAME2
---------- ----------
2 2
3
SQL> select count(1) from tb1 where exists (select 1 from tb2 where tb1.t1=tb2.t2);
--只有T1=2的记录符合条件，与常规思维相悖
COUNT(1)
----------
1
SQL> select count(1) from tb1 where not exists (select 1 from tb2 where tb1.t1=tb2.t2);
--T1=1和T1 为NULL的记录都返回了，与常规思维相悖
COUNT(1)
----------
2
SQL> select count(1) from tb1 where exists (select 1 from tb2 where tb1.t1=tb2.t2 and t2 is not null);
--只返回T1=2的记录符合常规逻辑
COUNT(1)
----------
1
SQL> select count(1) from tb1 where not exists (select 1 from tb2 where tb1.t1=tb2.t2 and t2 is not null);
-- T1=1和T1 为NULL的记录都返回了，符合常规逻辑
COUNT(1)
----------
2
2.1.5外表无符合条件记录
SQL> select * from tb1;
T1 NAME1
---------- ----------
SQL> select * from tb2;
T2 NAME2
---------- ----------
2
2
3 3
SQL> select count(1) from tb1 where exists (select 1 from tb2 where tb1.t1=tb2.t2);
COUNT(1)
----------
0
SQL> select count(1) from tb1 where not exists (select 1 from tb2 where tb1.t1=tb2.t2);
COUNT(1)
----------
0
这个很好理解，源表无记录，无论条件真假，都不会有记录返回的。
2.1.6内表无符合条件记录
SQL> select * from tb1;
T1 NAME1
---------- ----------
1 1
2 2
3
SQL> select * from tb2;
T2 NAME2
---------- ----------
SQL> select count(1) from tb1 where exists (select 1 from tb2 where tb1.t1=tb2.t2);
--符合常规逻辑
COUNT(1)
----------
0
SQL> select count(1) from tb1 where not exists (select 1 from tb2 where tb1.t1=tb2.t2);
--符合常规逻辑
COUNT(1)
----------
3
2.2 exists使用总结
1. exists的原理
select * from tb1 where exists ( select null from tb2 where col1 = col2 )
相当于：
for t1 in ( select * from tb1 )
loop
if ( exists ( select 1 from tb2 where t2 = tb1.col1 )
then
OUTPUT THE RECORD
end if
end loop
其中：exists ( select null from tb2 where t2 = tb1.col1 )返回是一个布尔值，not exists只是对exists子句返回对布尔值取非，这与in和not in是有本质区别的（not in是对in表达式取非，转换成另一种等价表达式）
2. exists运算中，当t2列表中有空值时，得到结果与把空值从列表中去掉是一样当，也就是说，可以把col2列表的空值忽略。
3. 只需记住null=null和null<>null在oracle都不成立，即可理解exists/not exists运算不符合常规思维的地方。
