举例
在 数 据 库 中， 空 值 用 来 表 示 实 际 值 未 知 或 无 意 义 的 情 况。 在 一 个 表 中， 如 果 一 行 中 的 某 列 没 有 值， 那 么 就 称 它 为 空 值（NULL）。 任 何 数 据 类 型 的 列, 只 要 没 有 使 用 非 空（NOT NULL） 或 主 键（PRIMARY KEY） 完 整 性 限 制， 都 可 以 出 现 空 值。 在 实 际 应 用 中， 如 果 忽 略 空 值 的 存 在， 将 会 造 成 造 成 不 必 要 的 麻 烦。
---- 例 如， 在 下 面 的 雇 员 表（EMP） 中， 雇 员 名（ENAME） 为KING 的 行， 因 为KING 为 最 高 官 员（PRESIDENT）， 他 没 有 主 管（MGR）， 所 以 其MGR 为 空 值。 因 为 不 是 所 有 的 雇 员 都 有 手 续 费（COMM）， 所 以 列COMM 允 许 有 空 值， 除300、500、1400、0 以 外 的 其 它 各 行COMM 均 为 空 值。
EMPNO ENAMEJOB MGR HIREDATESAL COMMDEPTNO
---- -------- -------- --------- -------- ------ ---------
7369 SMITH CLERK 7902 17-DEC-80800 20
7499 ALLEN SALESMAN7698 20-FEB-811600300 30
7521 WARDSALESMAN7698 22-FEB-811250500 30
7566 JONES MANAGER 7839 02-APR-812975
20
7654 MARTINSALESMAN7698 28-SEP-811250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-812850
30
7782 CLARK MANAGER 7839 09-JUN-812450
10
7788 SCOTT ANALYST 7566 09-DEC-823000
20
7839 KINGPRESIDENT17-NOV-815000
10
7844 TURNERSALESMAN7698 08-SEP-8115000 30
7876 ADAMS CLERK 7788 12-JAN-831100
20
7900 JAMES CLERK 7698 03-DEC-81 950
30
7902 FORDANALYST 7566 03-DEC-813000
20
7934 MILLERCLERK 7782 23-JAN-821300
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 ENAMEJOB MGR HIREDATESALCOMMDEPTNO
--------- ---------- --------- --------- --------- ---------
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 ENAMEJOBMGR HIREDATE SALCOMMDEPTNO
---------- --------- --------- --------- ---------
7839 KINGPRESIDENT17-NOV-81500010
---- 第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 ENAMEJOBMGRHIREDATESALCOMMDEPTNO
---------- --------- --------- --------- ---------
7844 TURNER SALESMAN7698 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
SALCOMM
---------- --------- ---------
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 salselect 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
SALCOMMSAL+COMM
---------- --------- ------------- --------- --------- ---------
SMITH 7902 SMITH7902 800
ALLEN 7698 ALLEN7698
1600 3001900
WARD
7698 WARD7698 1250 5001750
JONES 7839 JONES7839
2975
MARTIN
7698 MARTIN7698 125014002650
BLAKE 7839 BLAKE7839
2850
CLARK 7839 CLARK7839
2450
SCOTT 7566 SCOTT7566
3000
KING
KING
5000
TURNER
7698 TURNER7698 1500 01500
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 salselect count(comm),sum(comm),avg(comm) from emp;
COUNT(COMM) SUM(COMM) AVG(COMM)
----------- --------- ---------
42200 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
2200157.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;
DEPTNOSUM(SAL) SUM(COMM) SUM(SAL+COMM) SUM(SAL)
+SUM(COMM) SUM(NVL(SAL,0)+NVL(COMM,0))
--------- --------- --------- ------------- -------
10 8750
8750
20 10875
10875
30 94002200 780011600 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.
|
