Oracle技术网—漫谈oracle中的空值 - [ oracle知识库 ] - 在线原生手册

目录搜索

文字

漫谈oracle 中的空值

摘自：计算机世界日报

河北省统计局贾书民

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

---- 例如，在下面的雇员表（EMP）中，雇员名（ENAME）为KING 的行，因为KING 为最高官员（PRESIDENT），他没有主管（MGR），所以其MGR 为空值。因为不是所有的雇员都有手续费（COMM），所以列COMM 允许有空值，除300、500、1400、0 以外的其它各行COMM 均为空值。

EMPNO ENAME    JOB       MGR HIREDATE    SAL   COMM  DEPTNO
---- -------- -------- --------- -------- ------ ---------
7369 SMITH     CLERK     7902 17-DEC-80  800           20
7499 ALLEN     SALESMAN  7698 20-FEB-81  1600    300   30
7521 WARD      SALESMAN  7698 22-FEB-81  1250    500   30
7566 JONES     MANAGER   7839 02-APR-81  2975          20
7654 MARTIN    SALESMAN  7698 28-SEP-81  1250   1400   30
7698 BLAKE     MANAGER   7839 01-MAY-81  2850          30
7782 CLARK     MANAGER   7839 09-JUN-81  2450          10
7788 SCOTT     ANALYST   7566 09-DEC-82  3000          20
7839 KING      PRESIDENT      17-NOV-81  5000          10
7844 TURNER    SALESMAN  7698 08-SEP-81  1500      0   30
7876 ADAMS     CLERK     7788 12-JAN-83  1100          20
7900 JAMES     CLERK     7698 03-DEC-81   950          30
7902 FORD      ANALYST   7566 03-DEC-81  3000          20
7934 MILLER    CLERK     7782 23-JAN-82  1300          10

---- 本文将以上述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 >select * from emp where empno=1;
EMPNO ENAME  JOB   MGR HIREDATE    SAL    COMM    DEPTNO
--------- ---------- --------- --------- --------- ---------
        1 JIA         1000

---- 可以看到新插入的一行，除job 和comm 为空值外，mgr、hiredate、deptno 三列由于插入时未涉及，也为空值。

---- 使用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 条件中，Oracle 认为结果为NULL 的条件为FALSE，带有这样条件的select 语句不返回行，也不返回错误信息。

---- 例如查询EMP 表中MGR 为NULL 的行：

SQL >select * from emp where mgr='';  
no rows selected
SQL >select * from emp where mgr=null;  
no rows selected
SQL >select * from emp where mgr is null;
EMPNO ENAME  JOB  MGR HIREDATE   SAL  COMM  DEPTNO
---------- --------- --------- --------- ---------
7839 KING  PRESIDENT  17-NOV-81  5000        10

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

---- 三、空值和操作符

---- 1. 空值和逻辑操作符

---- 逻辑操作符

---- 表达式

---- 结果

AND
NULL AND TRUE
NULL

NULL AND FALSE
FALSE

NULL AND NULL
NULL
OR
NULL OR TRUE
TRUE

NULL OR FALSE
NULL

NULL OR NULL
NULL
NOT
NOT NULL
NULL

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

---- 虽然在where 条件中，Oracle 认为结果为NULL 的WHERE 条件为FALSE，但在条件表达式中NULL 不同于FALSE。例如在NOT （ NULL AND FALSE ）和NOT （ NULL AND NULL ）二者中仅有一处FALSE 和TRUE 的区别，但NOT （ NULL AND FALSE ）的结果为 TRUE，而NOT （ NULL AND NULL ）的结果为NULL。

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

SQL > select * from emp where not comm=null and comm!=0;
no rows selected
SQL > select * from emp where not ( not comm=null and comm!=0 );
EMPNO ENAME  JOB      MGR  HIREDATE  SAL  COMM  DEPTNO
 ---------- --------- --------- --------- ---------
7844 TURNER SALESMAN  7698 08-SEP-81 1500   0     30

---- 第一个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，条件等价于NOT FALSE，结果为TRUE。所以，最终结果返回行COMM 等于0 的行。

---- 2. 空值和比较操作符

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

（2）=、!=、>=、、select ename,sal,comm from emp where sal >comm;
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,NULL);
ENAME            MGR
---------- ---------
SMITH           7902

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

SQL >select deptno from emp where deptno not in ('10',NULL);
no rows selected

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

---- （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 或FALSE，所以不返回行。

---- （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 SQL >select ename,sal from emp where sal not between null and 3000; ENAME SAL ---------- --------- KING 5000

---- 条件"sal not between null and 3000" 等价于sal3000, 类似前述（3）的第一句，结果返回sal>3000 的行。

---- 下表为比较操作符和空值的小结：

---- 比较操作符

---- 表达式（例：A、B 是NULL、C=10）

---- 结果

IS NULL、IS NOT NULL
A IS NULL
TRUE

A IS NOT NULL
FALSE

C IS NULL
FALSE

C IS NOT NULL
TRUE
=、!=、>=、、 NULL
NULL

C = NULL
NULL

C > NULL
NULL
IN (=ANY)
A IN (10,NULL)
NULL

C IN (10,NULL)
TRUE

C IN (20,NULL)
NULL
NOT IN 
( 等 价 于 ！=ALL)
A NOT IN (20,NULL)
NULL

C NOT IN (20,NULL)
FALSE

C NOT IN (10,NULL)
NULL
ANY，SOME
A > ANY(5,NULL)
NULL

C > ANY(5,NULL)
TRUE

C > ANY(15,NULL)
NULL
ALL
A > ALL(5,NULL)
NULL

C > ALL(5,NULL)
NULL

C > ALL(15,NULL)
FALSE
(NOT)BETWEEN
A BETWEEN 5 AND NULL
NULL

C BETWEEN 5 AND NULL
NULL

C BETWEEN 15 AND NULL
FALSE

A NOT BETWEEN 5 AND NULL
NULL

C NOT BETWEEN 5 AND NULL
NULL

C NOT BETWEEN 15 AND NULL
TRUE

---- 3、空值和算术、字符操作符

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

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

SQL >select ename,mgr,ename||mgr,sal,comm,sal+comm from emp;
ENAME            MGR ENAME||MGR          SAL      COMM  SAL+COMM
---------- --------- ------------- --------- --------- ---------
SMITH           7902 SMITH7902           800 
ALLEN           7698 ALLEN7698          1600       300      1900
WARD            7698 WARD7698           1250       500      1750
JONES           7839 JONES7839          2975 
MARTIN          7698 MARTIN7698         1250      1400      2650
BLAKE           7839 BLAKE7839          2850 
CLARK           7839 CLARK7839          2450 
SCOTT           7566 SCOTT7566          3000 
KING                 KING               5000 
TURNER          7698 TURNER7698         1500         0      1500
ADAMS           7788 ADAMS7788          1100 
JAMES           7698 JAMES7698           950 
FORD            7566 FORD7566           3000 
MILLER          7782 MILLER7782         1300

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

---- 四、空值和函数

---- 1 ．空值和度量函数

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

SQL > select ename,sal,comm,abs(comm) from emp where sal
----
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.

漫 谈oracle 中 的 空 值

河 北 省 统 计 局 贾 书 民

漫谈oracle 中的空值

河北省统计局贾书民