Oracle中的NULL（八）

最近在論壇上經常看到，很多人提出和NULL有關的問題。NULL其實是資料庫中特有的型別，Oracle中很多容易出現的錯誤都是和NULL有關的。

打算簡單的總結一下NULL的相關知識。

這一篇描述一下在SQL和PLSQL中一些處理NULL的一些問題。

Oracle中的NULL（一）：http://yangtingkun.itpub.net/post/468/244434

Oracle中的NULL（二）：http://yangtingkun.itpub.net/post/468/245107

Oracle中的NULL（三）：http://yangtingkun.itpub.net/post/468/245259

Oracle中的NULL（四）：http://yangtingkun.itpub.net/post/468/245697

Oracle中的NULL（五）：http://yangtingkun.itpub.net/post/468/247492

Oracle中的NULL（六）：http://yangtingkun.itpub.net/post/468/251496

Oracle中的NULL（七）：http://yangtingkun.itpub.net/post/468/258467

NULL的最大的特點就是兩個NULL是不相等的。如果用等號來判斷兩個NULL是否相等得到的結果一定是NULL。從唯一約束的特點也可以看到，對於建立了唯一約束的列，Oracle允許插入多個NULL值，這時因為Oracle不認為這些NULL是相等的。

SQL> CREATE TABLE T (ID NUMBER, CONSTRAINT UN_T UNIQUE(ID));

表已建立。

SQL> INSERT INTO T VALUES (1);

已建立 1 行。

SQL> INSERT INTO T VALUES (1);
INSERT INTO T VALUES (1)
*
ERROR 位於第 1 行:
ORA-00001: 違反唯一約束條件 (YANGTK.UN_T)

SQL> INSERT INTO T VALUES (NULL);

已建立 1 行。

SQL> INSERT INTO T VALUES (NULL);

已建立 1 行。

但是有的時候，Oracle會認為NULL是相同的，比如在GROUP BY和DISTINCT操作中。這個時候，Oracle會認為所有的NULL都是一類的。

還有一種情況，就是在DECODE函式中。如果表示式為DECODE(COL, NULL, 0, 1)，那麼如果COL的值為NULL，Oracle會認為這種情況與第二個引數的NULL值相匹配，會返回0。不過這裡只是給人感覺NULL值是相等的，Oracle在實現DECODE函式的時候，仍然是透過IS NULL的方式進行的判斷。

對於大多數的常用函式來說，如果輸入為NULL，則輸出也是NULL。NVL、NVL2、DECODE和||操作是個例外。他們在輸入引數為NULL的時候，結果可能不是NULL。不過歸結其原因是因為，這些函式都有多個引數，當多個引數不全為NULL時，結果可能不是NULL，如果輸入引數均為NULL，那麼得到的輸出結果也是NULL。

NULL還有一個特點，就是一般聚集函式不會處理NULL值。不管是MAX、MIN、AVG還是SUM，這些聚集函式都不會處理NULL。注意這裡說的不會處理NULL，是指聚集函式會直接忽略NULL值記錄的存在。除非是聚集函式處理的列中包含的全部記錄都是NULL，這種情況下，上面這些聚集函式會返回NULL值。

SQL> DELETE T WHERE ID = 1;

已刪除 1 行。

SQL> SELECT NVL(TO_CHAR(ID), 'NULL') FROM T;

NVL(TO_CHAR(ID),'NULL')
----------------------------------------
NULL
NULL

SQL> SELECT MAX(ID) FROM T;

MAX(ID)
----------

SQL> SELECT AVG(ID) FROM T;

AVG(ID)
----------

SQL> INSERT INTO T VALUES (1);

已建立 1 行。

聚集函式中比較特殊的是COUNT，第一個特殊點是COUNT不會返回NULL值，即使表中沒有記錄，或者COUNT(COL)中，COL列的記錄全為NULL，COUNT也會返回0值而不是NULL。第二個特殊點就是COUNT(*)或COUNT(常量)的形式。這種形式使得COUNT可以計算包含NULL記錄在內的記錄總數。

SQL> SELECT COUNT(*), COUNT(1), COUNT('A'), COUNT(ID), COUNT(NULL) FROM T;

COUNT(*) COUNT(1) COUNT('A') COUNT(ID) COUNT(NULL)
---------- ---------- ---------- ---------- -----------
3 3 3 1 0

最後簡單說一下AVG，AVG(COL)等價於SUM(COL)/COUNT(COL)，不等價於SUM(COL)/COUNT(*)：

SQL> SELECT AVG(ID), SUM(ID)/COUNT(ID), SUM(ID)/COUNT(*) FROM T;

AVG(ID) SUM(ID)/COUNT(ID) SUM(ID)/COUNT(*)
---------- ----------------- ----------------
1 1 .333333333