ITPub博客

首页 > Linux操作系统 > Linux操作系统 > In和exists使用及性能分析(一):in的使用

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

原创 Linux操作系统 作者:space6212 时间:2019-06-04 16:48:07 0 删除 编辑

本节主要讨论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类似

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/231499/viewspace-63763/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2005-01-25

  • 博文量
    202
  • 访问量
    142980