04.17 oracle的空值介绍

在oracle数据库中,空值用来表示实际值未知或无意义的情况. 在一个表中,如果一行中的某列没有值,那么就称它为空值（NULL）.任何数据类型的列, 只要没有使用非空（NOT NULL）或主键（PRIMARY KEY）完整性限制， 都 可以出现空值。

一、空值的生成与特点。

如果一列没有非空（NOTNULL）完整性限制，那么其缺省的值为空值，即如果插入一行时未指定该列的值，则其值为空值。

空值具有以下特点：

1）等价于没有任何值。2）与0、空字符串或空格不同。3）在where条件中,Oracle认为结果为NULL的条件为FALSE，带有这样条件的select语句不返回行，并且不返回错误信息。但NULL和FALSE是不同的。4）排序时比其他数据都大。5）空值不能被索引。

二、空值和操作符

1.空值和比较操作符

（1）IS[NOT]NULL：是用来测试空值的唯一操作符（见"空值的测试"）。

sal或comm为空值的行，sal>comm比较结果为NULL，所以凡是sal或comm为空值的行都没有返回。

（3）IN和NOTIN操作符

在上述语句中，条件"mgr in (7902,NULL)"等价于mgr=7902 or mgr=NULL。对于表EMP中的任意一行，如果mgr为NULL，则上述条件等价于NULL OR NULL，即为NULL；如果mgr为不等于7902的数值，则上述条件等价于FALSEORNULL，即为NULL；如果mgr等于7902，则上述条件等价于TRUE OR NULL，即为TRUE。所以，最终结果能返回mgr等于7902的行

在上述语句中，条件"deptno not in('10',NULL)"等价于deptno!='10' and deptno!=NULL，对于EMP表中的任意一行，条件的结果只能为NULL或FALSE，所以不返回行。

（4）any,some

条件"sal>any(3000,null)"等价于sal>3000 or sal>null。类似前述（3）第一句，最终结果返回所有sal>3000的行。

（5）All

条件"sal>all(3000,null)"等价于sal>3000 and sal>null,结果只能为NULL或FALSE，所以不返回行。

（6）（not）between

条件"sal between null and 3000"等价于sal>=null and sal<=3000,结果只能为NULL或FALSE，所以不返回行。

2、空值和算术、字符操作符

（1）算术操作符：空值不等价于0，任何含有空值的算术表达式其运算结果都为空值，例如空值加10为空值。

（2）字符操作符||：因为ORACLE目前处理零个字符值的方法与处理空值的方法相同（日后的版本中不一定仍然如此），所以对于||，空值等价于零个字符值。例：

我们可以看到，凡mgr为空值的，ename||mgr结果等于ename；凡是comm为空值的行，sal+comm均为空值。

三、空值和函数

1．空值和度量函数

对于度量函数，如果给定的参数为空值，则其（NVL、TRANSLATE除外）返回值为空值。如下例中的ABS（COMM），如果COMM为空值，ABS(COMM)为空值。

2.空值和组函数

组函数忽略空值。在实际应用中，根据需要可利用nvl函数用零代替空值。例：

第一个SELECT语句忽略COMM为空值的行，第二个SELECT语句使用NVL函数统计了所有的COMM，所以它们统计的个数、平均值都不相同。另外需要注意的是，在利用组函数进行数据处理时，不同的写法具有不同的不同含义，在实际应用中应灵活掌握。例如：

可以看到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.空值在排序时大于任何值。

2.空值不能被索引。虽然在某列上建立了索引，但是对该列的空值查询来说，因为空值没有被索引，所以不能改善查询的效率。例如下面的查询不能利用在MGR列上创建的索引。

另外正是因为空值不被索引，所以可在含有空值的列上建立唯一性索引（UNIQUEINDEX）。