今天,一同事问我,有个问题很奇怪,他写的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
参与的实验数据:
--创建测试表
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进行空值比较。
就是涉及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/