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、空字符串或空格不同。    
  *   在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.  

转载于:https://www.cnblogs.com/raymond19840709/archive/2007/11/30/978249.html