SQL和PLSQL中处理NULL的一些问题

SQL和PLSQL中处理NULL的一些问题

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

--------------------------------------------------------------------------------