漫談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）=、!=、>=、<=、>、<
SQL>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<=3000, 結果只能為NULL或FALSE，所以不返回行。
SQL>select ename,sal from emp where sal not between null and 3000;
ENAME SAL
---------- ---------
KING 5000
條件“sal not between null and 3000”等價于sal<null or sal>3000,類似前述（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
=、!=、>=、<=、>、<
A = NULL
NULL
A > 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,enamemgr,sal,comm,sal+comm from emp;
ENAME MGR ENAMEMGR 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為空值的，enamemgr結果等于ename；凡是comm為空值的行，sal+comm均為空值。
四、空值和函數
1．空值和度量函數
對于度量函數，假如給定的參數為空值，則其（NVL、TRANSLATE除外）返回值為空值。如下例中的ABS（COMM），假如COMM為空值，ABS(COMM)為空值。
SQL> select ename,sal,comm,abs(comm) from emp where 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.
_____________________________________________
