oracle中和空值等价的是,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/，如需转载，请注明出处，否则将追究法律责任。