漫談oracle中的空值(轉)

漫談oracle中的空值(轉)[@more@]

在資料庫中，空值用來表示實際值未知或無意義的情況。在一個表中，如果一行中的某列沒有值，那麼就稱它為空值（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”等價於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

=、!=、>=、<=、>、<

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,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<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 create