ORACLE的count与空值比较

  今天，一同事问我，有个问题很奇怪，他写的SQL语句不统计null值，怎么一回事，看下面重现：
 参与的实验数据：
--创建测试表
create table mytab(
col1 varchar2(10),
col2 varchar2(10),
col3 varchar2(10)
);
--插入测试数据
insert into mytab values ('10010','b1','c1');
insert into mytab values ('10011','b2','c2');
insert into mytab values ('10012','b3','c3');
insert into mytab values ('10012','b4','c4');
insert into mytab values ('10012','b4','c5');
insert into mytab values ('10012','b4','c6');
insert into mytab values ('10013','','');
insert into mytab values ('10013','','c7');
insert into mytab values ('10012','','c9');
insert into mytab values ('10012','b9','');

 --同事觉得奇怪的SQL语句及执行结果：
select 'A' as "编号", count(*) from mytab
union
select 'B' as "编号",count(*) from mytab where col2 in('b4','b3')
union 
select 'C' as "编号",count(*) from mytab where col2 not in('b4','b3') 
union 
select 'D' as "编号",count(*) from mytab where col2 is null;
查询结果：
  编号 COUNT(*)
  A     10
  B     4
  C     3
  D     3
 同事觉得编号为C的统计值，应该包含编号为D的统计值，但是，为什么编号C没有包括编号D的值呢？
其实，很简单：
select 'C' as "编号",count(*) from mytab where col2 not in('b4','b3')
union 
select 'H' as "编号",count(*) from mytab where col2 != 'b4' and col2 !='b3'
union
select 'I' as "编号",count(*) from mytab where col2 not in('b4','b3') or col2 is null; 
编号 COUNT(*)
 C   3
 H   3
 I    6

编号为C的SQL与编号为H的SQL等价，所以C、H的查询结果也相同，从编号为I的查询结果，我们就能发现问题在哪，
就是涉及null的比较，正确方法是：null比较相等用is，比较不等用is not，而不能用=或<>，否则统计将排除空值，有下列SQL语句为证：
select 'C' as "编号",count(*) from mytab where col2 not in('b4','b3') 
union 
select 'D' as "编号",count(*) from mytab where col2 is null
union
select 'E' as "编号", count(*) from mytab where col2 is not null
union
select 'G' as "编号",count(*) from mytab where col2 = null
union
select 'H' as "编号",count(*) from mytab where col2 <> null
union
select 'I' as "编号",count(*) from mytab where col2 not in('b4','b3') or col2 is null; 
编号 COUNT(*)
 C   3
 D   3
 E   7
 G   0
 H   0
 I    6
编号为G和编号为H的查询结果说明null值使用=或<>进行统计时永远都为0，只有is 或is not才能统计Null值列名，这是个陷阱。
我想起了，自己JAVA面试的时候，经理就问了这么一个问题，就是涉及空值的where条件语句怎么写，答案的关键就是要用is 或
is not进行空值比较。

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

转载于:http://blog.itpub.net/29357786/viewspace-2121449/