河北省统计局 贾书民

____在数据库中,空值(NULL)用来表示实际值未知或无意义的情况。在一个表中,如果一行中的某列没有值,那么就称它为空值。任何数据类型的列,只要没有使用非空(NOT NUL L)或主键(PRIMARY KEY)完整性限制,都可以出现空值。在实际应用中,如果忽略空值的存在,将会造成很多麻烦。例如,在下面的雇员表(EMP)中,雇员名(ENAME)为KING的行,因为 KING为最高官员(PRESIDENT),他没有主管(MGR),所以其MGR为空值。

表1

____本文将以上述EMP表为例,具体讨论一下空值在日常应用中所具有的一些特性。

____空值的生成及特点

____1. 空值的生成

____如果一列没有非空(NOT NULL)完整性限制,那么其缺省的值为空值,即如果插入一行时未指定该列的值,则其值为空值。

____使用SQL语句INSERT插入行,凡未涉及到的列,其值为空值;涉及到的列,如果其值确实为空值,插入时可以用NULL来表示(对于字符型的列,也可以用‘’来表示)。

____例:插入一行,其EMPNO为1、ENAME为‘JIA’、SAL为10000、job和comm为空值:

____SQL>insert into emp(empno,ename,job,sal,comm) values(1,‘JIA’,NULL,1000 ,NULL);

____使用SQL语句UPDATE来修改数据,空值可用NULL来表示,例:

____SQL>update emp set ename=NULL,sal=NULL where empno=1;

____2. 空值的特点

____空值具有以下特点:

____· 等价于没有任何值;

____· 与 0、空字符串或空格不同;

____· 在where条件中, Oracle认为结果为NULL的条件为FALSE,带有这样条件的select 语句不返回行,并且不返回错误信息,但NULL和FALSE是不同的;

____· 排序时比其他数据都大;

____· 空值不能被索引。

____空值的测试

____因为空值表示缺少数据,所以空值和其它值没有可比性,即不能用等于、不等于、大于或小于和其它数值比较,当然也包括空值本身(但是在decode中例外,两个空值被认为是等价)。测试空值只能用比较操作符IS NULL 和IS NOT NULL。如果使用带有其它比较操作符的条件表达式,并且其结果依赖于空值,那么其结果必定是NULL。在where条件中,Or acle认为结果为NULL的条件为FALSE,带有这样条件的select语句不返回行,也不返回错误信息。例如查询EMP表中MGR为NULL的行:

表2

____第1、2句写法不妥,WHERE条件结果为NULL,不返回行。第三句正确,返回MGR为空值的行。

____空值和操作符

____1. 空值和逻辑操作符

表3

____可以看到,在真值表中,除NULL AND FALSE 结果为FALSE、NULL OR TRUE结果为TRUE 以外,其它结果均为NULL。

____虽然在where条件中,Oracle认为结果为NULL的where条件为FALSE,但在条件表达式中 NULL不同于FALSE。例如在NOT( NULL AND FALSE )和NOT ( NULL AND NULL )中,前者结果为 TRUE,而后者结果为NULL。

____下面举例说明空值和逻辑操作符的用法:

表4

____第一个Select语句,条件"not comm=null and comm!=0"等价于NULL AND COMM!=0。对于任意一行,如果COMM为不等于0的数值,条件等价于NULL AND TRUE,结果为NULL;如果 COMM等于0,条件等价于NULL AND FALSE,结果为FALSE。所以,最终结果不返回行。

____第二个Select语句的条件为第一个Select语句条件的"非"(NOT),对于任意一行,如果 COMM为不等于0的数值,条件等价于NOT NULL,结果为NULL;如果COMM等于0,条件等价于NO T FALSE,结果为TRUE。所以,最终结果返回行COMM等于0的行。

____2. 空值和比较操作符

____(1) IS [NOT] NULL:是用来测试空值的唯一操作符。

____(2) =、!=、>=、<=、>、<　SQL>select ename,sal,comm from emp where sal>co mm;

____ENAME　　　　　 SAL　　　　　　 COMM

____---------------------------------------------

____ALLEN　　　　　 1600　　　　　　300

____WARD　　　　　　1250　　　　　　500

____TURNER　　　　　1500　　　　　　0

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

____(3) IN和NOT IN操作符 SQL>select ename,mgr from emp where mgr in (7902,NU LL);

____ENAME　　　　　 MGR

____SMITH　　　　　 7902

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

____(4) any,some SQL>select ename,sal from emp where sal> any(3000,null);

____ENAME　　　　　 SAL

____KING　　　　　　5000

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

____(5) All　SQL>select ename,sal from emp where sal> all(3000,null);

____no rows selected

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

____(6) (not)between　 SQL>select ename,sal from emp where sal between null and 3000;

____no rows selected

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

____下表为比较操作符和空值的小结:

表5

____3. 空值和算术、字符操作符

____(1) 算术操作符:空值不等价于0,任何含有空值的算术表达式其运算结果都为空值, 例如空值加10为空值。

____(2) 字符操作符||:因为Oracle目前处理零个字符值的方法与处理空值的方法相同( 以后的版本中不一定仍然如此),所以对于||,空值等价于零个字符值。

____空值和函数

____1. 空值和度量函数

____对于度量函数,如果给定的参数为空值,则其(NVL、TRANSLATE除外)返回值为空值。如本文所述例子中的ABS(COMM),如果COMM为空值,ABS(COMM)为空值。

____2. 空值和组函数

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

表6

____第一个SELECT语句忽略COMM为空值的行,第二个SELECT语句使用NVL函数统计了所有的COMM,所以它们统计的个数、平均值都不相同。

____另外需要注意的是,在利用组函数进行数据处理时,不同的写法具有不同的含义,在实际应用中应灵活掌握。例如:

表7

____可以看到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;在S UM(NVL(SAL,0)+NVL(COMM,0))里,SAL、COMM中的NULL按0处理。

____空值的其它特性

____1. 空值在排序时大于任何值。

____2. 空值不能被索引。虽然在某列上建立了索引,但是对该列的空值查询来说,因为空值没有被索引,所以不能改善查询的效率。

____另外正是因为空值不被索引,所以可在含有空值的列上建立唯一性索引(UNIQUE IND EX)。例如,可以在EMP表的COMM列上建立唯一性索引:

____SQL>　create unique index emp_comm on emp(comm);

____Index created.