转载地址:http://blog.csdn.net/luocm/article/details/2511341
一个老生常谈的问题,前人已有描述,读者可参考http://www.51cto.com/art/200511/10681.htm(Oracle中null的使用详解)。不过,在实际编写pl/sql语,不小心仍旧会犯错误,有鉴于此,记录下来以便日后查询方便,避免犯低级错误。
本文以Oracle中null值为例,其它数据库如DB2,SQLServer,Sybase等大同小异。
1、四则运算
包含null值的+ - * /二元运算符处理后,输出值仍旧为null。即 1 + null, 1 - null, 1 * null, 1 / null, null + null, ...结果值为null。
-- 返回结果为NULL值
SELECT 1 + NULL FROM dual;
2、逻辑运算
包含null值的> < >= <=二元运算符处理后,输出值为false。即 1 > null, 1 < null, 1 >= null, 1 <= null, 1 <> null, null = null, 。
-- 结果集为空
SELECT * FROM dual WHERE 1 <> NULL;
若使用IS NULL或者IS NOT NULL判断,则符合预期。1 IS NOT NULL返回true,null is null返回true。
-- 结果集返回1条记录
SELECT * FROM dual WHERE 1 is not NULL;
3、聚合运算
若聚合的列全部为null值,则聚合函数输出null值,否则输出非空值的聚合运算结果。
-- 返回null, null, null, null
SELECT MAX(c1), MIN(c1), SUM(c1), AVG(c1)
FROM
(
SELECT NULL AS c1 FROM dual
UNION ALL
SELECT NULL AS c1 FROM dual
UNION ALL
SELECT NULL AS c1 FROM dual
)
WHERE 1 = 1;
-- 返回2, 1, 3, 1.5
SELECT MAX(c1), MIN(c1), SUM(c1), AVG(c1)
FROM
(
SELECT NULL AS c1 FROM dual
UNION ALL
SELECT 1 AS c1 FROM dual
UNION ALL
SELECT 2 AS c1 FROM dual
)
WHERE 1 = 1;
4、内嵌函数
对null值,调用Oracle内嵌函数(字符串/数值/日期等类型操作函数)基本上全部返回null值。
-- 返回null, null
SELECT substr(NULL, 1, 3), to_date(NULL, 'yyyymmdd')
FROM dual
对一些允许null值的字段进行操作时,要小心了,有时因为不注意导致最终查询结果与预期不一致,这很可能就是没有正确操作null值的原因造成的。
自己做的一些小测试:
/*
create table test_null (
id varchar2(32),
value number(8,2),
deptno varchar2(32)
);
create index inx_id on test_null(id);*/
/*
insert into test_null values ('1',50,'2');
insert into test_null values ('2',40,'2');
insert into test_null(id,deptno) values ('3','1');
insert into test_null(id,deptno) values ('4','2');
insert into test_null values ('5',50,'3');
insert into test_null values ('6',60,'3');
insert into test_null values ('7',10,'1');
insert into test_null values ('8',20,'1');
insert into test_null values ('9',30,'1');
commit;*/
--验证
--1.使用count时,例如count(字段a),如果字段a为null,则count所显示的记录数不正确
-- count时所得到的结果为字段a不为空的记录数
select * from test_null;
select count(value) from test_null;
--2.null会对聚合函数avg有影响,为了避免影响,尽量在处理之前使用nvl函数处理
select t.*,t.rowid from test_null t order by deptno;
select sum(value),deptno from test_null group by deptno;
select avg(value),deptno from test_null group by deptno;
select avg(nvl(value,0)),deptno from test_null group by deptno;
/*drop table test_null purge;*/
一个老生常谈的问题,前人已有描述,读者可参考http://www.51cto.com/art/200511/10681.htm(Oracle中null的使用详解)。不过,在实际编写pl/sql语,不小心仍旧会犯错误,有鉴于此,记录下来以便日后查询方便,避免犯低级错误。
本文以Oracle中null值为例,其它数据库如DB2,SQLServer,Sybase等大同小异。
1、四则运算
包含null值的+ - * /二元运算符处理后,输出值仍旧为null。即 1 + null, 1 - null, 1 * null, 1 / null, null + null, ...结果值为null。
-- 返回结果为NULL值
SELECT 1 + NULL FROM dual;
2、逻辑运算
包含null值的> < >= <=二元运算符处理后,输出值为false。即 1 > null, 1 < null, 1 >= null, 1 <= null, 1 <> null, null = null, 。
-- 结果集为空
SELECT * FROM dual WHERE 1 <> NULL;
若使用IS NULL或者IS NOT NULL判断,则符合预期。1 IS NOT NULL返回true,null is null返回true。
-- 结果集返回1条记录
SELECT * FROM dual WHERE 1 is not NULL;
3、聚合运算
若聚合的列全部为null值,则聚合函数输出null值,否则输出非空值的聚合运算结果。
-- 返回null, null, null, null
SELECT MAX(c1), MIN(c1), SUM(c1), AVG(c1)
FROM
(
SELECT NULL AS c1 FROM dual
UNION ALL
SELECT NULL AS c1 FROM dual
UNION ALL
SELECT NULL AS c1 FROM dual
)
WHERE 1 = 1;
-- 返回2, 1, 3, 1.5
SELECT MAX(c1), MIN(c1), SUM(c1), AVG(c1)
FROM
(
SELECT NULL AS c1 FROM dual
UNION ALL
SELECT 1 AS c1 FROM dual
UNION ALL
SELECT 2 AS c1 FROM dual
)
WHERE 1 = 1;
4、内嵌函数
对null值,调用Oracle内嵌函数(字符串/数值/日期等类型操作函数)基本上全部返回null值。
-- 返回null, null
SELECT substr(NULL, 1, 3), to_date(NULL, 'yyyymmdd')
FROM dual
对一些允许null值的字段进行操作时,要小心了,有时因为不注意导致最终查询结果与预期不一致,这很可能就是没有正确操作null值的原因造成的。
自己做的一些小测试:
/*
create table test_null (
id varchar2(32),
value number(8,2),
deptno varchar2(32)
);
create index inx_id on test_null(id);*/
/*
insert into test_null values ('1',50,'2');
insert into test_null values ('2',40,'2');
insert into test_null(id,deptno) values ('3','1');
insert into test_null(id,deptno) values ('4','2');
insert into test_null values ('5',50,'3');
insert into test_null values ('6',60,'3');
insert into test_null values ('7',10,'1');
insert into test_null values ('8',20,'1');
insert into test_null values ('9',30,'1');
commit;*/
--验证
--1.使用count时,例如count(字段a),如果字段a为null,则count所显示的记录数不正确
-- count时所得到的结果为字段a不为空的记录数
select * from test_null;
select count(value) from test_null;
--2.null会对聚合函数avg有影响,为了避免影响,尽量在处理之前使用nvl函数处理
select t.*,t.rowid from test_null t order by deptno;
select sum(value),deptno from test_null group by deptno;
select avg(value),deptno from test_null group by deptno;
select avg(nvl(value,0)),deptno from test_null group by deptno;
/*drop table test_null purge;*/