Oracle中的Null

在資料庫中，空值用來表示實際值未知或無意義的情況。在一個表中，如果一行中的某列沒有值，那麼就稱它為空白值（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、Null 字元串或空格不同。    
  *   在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,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   created.