ORACLE空值漫谈2

here sal<1500;
ename sal comm abs(comm)
---------- --------- --------- ---------
smith 800
ward 1250 500 500
martin 1250 1400 1400
adams 1100
james 950
miller 1300

2.空值和组函数

组函数忽略空值。在实际应用中，根据需要可利用nvl函数用零代替空值。例：
sql>select count(comm),sum(comm),avg(comm) from emp;
count(comm) sum(comm) avg(comm)
----------- --------- ---------
4 2200 550
sql>select count(nvl(comm,0)),sum(nvl(comm,0)),avg(nvl(comm,0))
from emp;
count(nvl(comm,0)) sum(nvl(comm,0)) avg(nvl(comm,0))
------------------ ---------------- ----------------
14 2200 157.14286

第一个select语句忽略comm为空值的行，第二个select语句使用nvl函数统计了所有的comm，所以它们统计的个数、平均值都不相同。

另外需要注意的是，在利用组函数进行数据处理时，不同的写法具有不同的不同含义，在实际应用中应灵活掌握。例如：
sql>select deptno,sum(sal),sum(comm), sum(sal+comm),sum(sal)+sum(comm),sum(nvl(sal,0)+nvl(comm,0))
from emp
group by deptno;
deptno sum(sal) sum(comm) sum(sal+comm) sum(sal)+sum(comm) sum(nvl(sal,0)+nvl(comm,0))
--------- --------- --------- ------------- ------------------ ---------------------------
10 8750 8750
20 10875 10875
30 9400 2200 7800 11600 11600

可以看到sum(sal+comm)、sum(sal)+sum(comm)、 sum(nvl(sal,0)+nvl(comm,0))的区别：sum(sal+comm)为先加然后计算各行的和，如果sal、comm中有一个为null，则该行忽略不计；sum(sal)+sum(comm)为先计算各行的合计然后再加，sal、comm中的null都忽略不计，但如果 sum(sal)、sum(comm)二者的结果之中有一个为null，则二者之和为null；在sum(nvl(sal,0)+nvl(comm,0))里，sal、comm中的null按0处理。

五、空值的其它特性

1.空值在排序时大于任何值。例如：
sql> select ename,comm from emp where deptno=''30'' order by comm；
ename comm
---------- ---------
turner 0
allen 300
ward 500
martin 1400
blake
james

2.空值不能被索引。虽然在某列上建立了索引，但是对该列的空值查询来说，因为空值没有被索引，所以不能改善查询的效率。例如下面的查询不能利用在mgr列上创建的索引。
sql>select ename from emp where mgr is null;
ename
----------
king

另外正是因为空值不被索引，所以可在含有空值的列上建立唯一性索引（unique index）。例如，可以在emp表的comm列上建立唯一性索引：
sql> create unique index emp_comm on emp(comm);
index created.