In和exists使用及性能分析(一)：in的使用

本节主要讨论in的原理及使用，以及常见的错误的观点。

一、in的使用
1.1 in的使用
1.1.1 内、外两表关联字段都非空
SQL> select * from tb1;
T1 NAME1
---------- ----------
1 1
2 2
SQL> select * from tb2;
T2 NAME2
---------- ----------
2 2
SQL> select count(1) from tb1 where t1 in (select t2 from tb2);
--T1=2的记录符合条件，结果符合常规思维
COUNT(1)
----------
1
SQL> select count(1) from tb1 where t1 not in (select t2 from tb2);
--T1=1的记录符合条件，结果符合常规思维
COUNT(1)
----------
1
1.1.2 当外表关联字段无空值、内表关联列表有空值时
SQL> select * from tb1;
T1 NAME1
---------- ----------
1 1
2 2
SQL> select * from tb2;
T2 NAME2
---------- ----------
2 2
3
SQL> select count(1) from tb1 where t1 in (select t2 from tb2);
--T1=2的记录符合条件，结果符合常规思维
COUNT(1)
----------
1
SQL> select count(1) from tb1 where t1 not in (select t2 from tb2);
--无记录符合条件，结果不符合常规思维
COUNT(1)
----------
0
SQL> select count(1) from tb1 where t1 in (select t2 from tb2 where t2 is not null);
--T1=2的记录符合条件，结果符合常规思维
COUNT(1)
----------
1
SQL> select count(1) from tb1 where t1 not in (select t2 from tb2 where t2 is not null);
--T1=2的记录符合条件，结果符合常规思维
COUNT(1)
----------
1
1.1.3当外表关联字段有空值、内表关联列表无空值时
SQL> select * from tb1;
T1 NAME1
---------- ----------
1 1
1
2 2
SQL> select * from tb2;
T2 NAME2
---------- ----------
2 2
3 3
SQL> select count(1) from tb1 where t1 in (select t2 from tb2);
--T1=2的记录符合查询条件，符合常规思维
COUNT(1)
----------
1
SQL> select count(1) from tb1 where t1 not in (select t2 from tb2);
--只有T1=2的记录符合查询条件，与常规思维相悖
COUNT(1)
----------
1
1.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 t1 in (select t2 from tb2);
--只有t1=2的记录符合条件，与常规思维相悖
COUNT(1)
----------
1
SQL> select count(1) from tb1 where t1 not in (select t2 from tb2);
--没有符合条件的记录，与常规思维相悖
COUNT(1)
----------
0
SQL> select count(1) from tb1 where t1 in (select t2 from tb2 where t2 is not null);
--只有t1=2的记录符合条件，符合常规思维
COUNT(1)
----------
1
SQL> select count(1) from tb1 where t1 not in (select t2 from tb2 where t2 is not null);
--只有t1=1的记录符合条件，与常规思维相悖
COUNT(1)
----------
1
1.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 t1 in (select t2 from tb2);
--无记录返回
COUNT(1)
----------
0
SQL> select count(1) from tb1 where t1 not in (select t2 from tb2);
--无记录返回
COUNT(1)
----------
0
这里很容易理解，无论条件成立与否，因为外表无记录，索引返回记录数为0
1.1.6 内表无记录
SQL> select * from tb1;
T1 NAME1
---------- ----------
1 1
2 2
SQL> select * from tb2;
T2 NAME2
---------- ----------
SQL> select count(1) from tb1 where t1 in (select t2 from tb2);
--所有记录都不符合条件，结果符合常规思维
COUNT(1)
----------
0
SQL> select count(1) from tb1 where t1 not in (select t2 from tb2);
--所有记录都符合条件，结果符合常规思维
COUNT(1)
----------
2
1.2 in运算总结：
1.2.1单列作in运算时
1. col in (v1,v2,...vn) 相当于 (col=v1 or col=v2 or ...col=vn)
col not in (v1,v2,...vn) 相当于 (col<>v1 and col<>v2 and ...col<>vn)
2. 当in 列表的记录数大于0，且in列表(vn)有null值时，则oracle在处理 in 的时候转换成：
(col=v1 or col=v2 or col=null or ...col=vn)
所以，in列表有null值不会引起与常规思维不一样的结果
3. 当not in 列表的记录数大于0，且not in列表有null值时，oracle在处理not in 当时候转换成：
(col<>v1 and col<>v2 and col<>null and ...col<>vn)，由于oracle中null是一个非常特殊的值，null与任何值做算术运算符比较都返回false，由于多个条件是用and连接的，所以这种情况整个条件是false，也就不会有记录返回。
这种情况是最容易搞混和不理解的。
4. 当in/not in 列表的记录数大于0，且col是null值时，null与任何值做算术运算符比较都返回false，根据in和not in的处理方式可知，无论是in运算还是not in 运算，oracle不会有任何记录返回。也就是说oracle在处理时相当于把col是null的记录忽略不作处理。
5. 当in/not in 列表的记录数为0时（注意与in/not in列表全为空不同），in运算相当于条件1=2；not in运算相当于1=1;
6. 在not in操作中，可以在子查询中加入col is not null使得有记录返回
1.2.2 多列作in运算时
1. (col1,col2) in ((v11,v12),(v21,v22),...(vn1,vn2))相当于
((col1=v11 and col2=v12) or (col1=v21 and col2=v21) ... or (col1=vn1 and col2=vn2))
(col1,col2) not in ((v11,v12),(v21,v22),...(vn1,vn2))相当于
((col1<>v11 or col2<>v12) and (col1<>v21 or col2<>v22) and ...(col1<>vn1 or col2<>vn2))
2. 多列in运算当原理和单列一样，把col1和col2看作一个整体col，把vn1和vn2看作一个整体vn就相当于单列作in运算了。
3. 多列运算比较特殊当两种情况是col1和col2都为null时，相当于单列中都col为null，oracle在处理这种情况时，相当于忽略这条记录；
当vn1和vn2都为null时，相当于单列运算中vn为null的情况，在作in运算时不会影响返回结果，但在作not in运算时不会有任何记录返回。
4. 当col1和col2中有一个为null时，作in运算时oracle相当于把该记录忽略不计，因为(null or null or null...)结果是null。（not in不能忽略）
5. 当col1和col2其中一个为null，且另一个的值在包含在not in列表中时，在作not in运算时oracle相当于把该记录忽略不计(null && true/false结果是null)。
6. 在not in操作中，可以在子查询加入((col1 is not null) or (col2 is not null))使得有结果返回
7. 以上小结适用于in/not in 列表的记录数大于0的情况
8. any、all运算与in类似