Oracle 中关于null值的概念以及逻辑运算

null介绍 NULL是数据库中特有的数据类型，当一条记录的某个列为NULL，则表示这个列的是未知的、是不确定的。既然是未知的，就有无数种的可能性。因此，NULL并不是一个确定的。 这是NULL的由来、也是NULL的基础，所有和NULL相关的操作的结果都可以从NULL的概

 null值介绍

        NULL是数据库中特有的数据类型，当一条记录的某个列为NULL，则表示这个列的值是未知的、是不确定的。既然是未知的，就有无数种的可能性。因此，NULL并不是一个确定的值。
       这是NULL的由来、也是NULL的基础，所有和NULL相关的操作的结果都可以从NULL的概念推导出来。
       判断一个字段是否为NULL，应该用IS NULL或IS NOT NULL，而不能用‘=’。对NULL的判断只能定性------（即是不是NULL（IS NULL/IS NOT NULL）），而不能定值。简单的说，由于NULL存在着无数的可能，因此两个NULL不是相等的关系，同样也不能说两个NULL就不相等，或者比较两个NULL的大小，这些操作都是没有意义，得不到一个确切的答案的。因此，对NULL的=、!=、>、=、        同理，对NULL进行+、-、*、/等操作的结果也是未知的，所以也是NULL。
       所以，很多时候会这样总结NULL，除了IS NULL、IS NOT NULL以外，对NULL的任何操作的结果还是NULL。
      上面这句话总结的很精辟，而且很好记，所以很多时候人们只记得这句话，而忘了这句话是如何得到的。其实只要清楚NULL的真正含义，在处理NULL的时候就不会出错。
说了怎么多，来看一个经典的例子：     

 SQL> CREATE OR REPLACE PROCEDURE P1 (P_IN IN NUMBER) AS
  2  BEGIN
  3   IF P_IN >= 0 THEN
  4    DBMS_OUTPUT.PUT_LINE('TRUE');
  5   ELSE
  6    DBMS_OUTPUT.PUT_LINE('FALSE');
  7   END IF;
  8  END;
  9  /
过程已创建。
SQL> CREATE OR REPLACE PROCEDURE P2 (P_IN IN NUMBER) AS
  2  BEGIN
  3   IF P_IN   4    DBMS_OUTPUT.PUT_LINE('FALSE');
  5   ELSE
  6    DBMS_OUTPUT.PUT_LINE('TRUE');
  7   END IF;
  8  END;
  9  /
过程已创建。

        上面两个过程是否是等价的？对于熟悉C或JAVA的开发人员来说，可能认为二者是等价的，但是在数据库中，则还要考虑到NULL的情况。
当输入为NULL时，可以看到上面两个过程不同的输出：

SQL> SET SERVEROUT ON
SQL> EXEC P1(NULL)
FALSE
PL/SQL 过程已成功完成。
SQL> EXEC P2(NULL)
TRUE
PL/SQL 过程已成功完成。
          输入为NULL时，上面两个过程中的判断的结果都是一样的，不管是NULL >= 0还是NULL           由于NULL所具有的特殊性，在处理数据库相关问题时应该对NULL的情况额外考虑，否则很容易造成错误。

关于NULL的布尔运算特点

         由于引入了NULL，在处理逻辑过程中一定要考虑NULL的情况。同样的，数据库中的布尔值的处理，也是需要考虑NULL的情况，这使得布尔值从原来的TRUE、FALSE两个值变成了TRUE、FALSE和NULL三个值。
下面是TRUE和FALSE两种情况进行布尔运算的结果：
AND操作：

AND	TRUE	FALSE
TRUE	TRUE	FALSE
FALSE	FALSE	FALSE

OR操作：

OR	TRUE	FALSE
TRUE	TRUE	TRUE
FALSE	TRUE	FALSE

上面是熟悉的TRUE和FALSE两个值进行布尔运算的结果，如果加上一个NULL的情况会怎样？NULL的布尔运算是否会像NULL的算术运算那样结果都是NULL呢？下面通过一个过程来进行说明：

SQL> SET SERVEROUT ON SIZE 100000
SQL> DECLARE
  2   TYPE T_BOOLEAN IS TABLE OF BOOLEAN INDEX BY BINARY_INTEGER;
  3   V_BOOL1 T_BOOLEAN;
  4   V_BOOL2 T_BOOLEAN;
  5  
  6   PROCEDURE P(P_IN1 BOOLEAN, P_IN2 BOOLEAN, P_OPERATOR IN VARCHAR2) AS
  7    V_RESULT BOOLEAN;
  8   BEGIN
  9    IF P_IN1 IS NULL THEN
10     DBMS_OUTPUT.PUT('NULL ');
11    ELSIF P_IN1 THEN
12     DBMS_OUTPUT.PUT('TRUE ');
13    ELSE
14     DBMS_OUTPUT.PUT('FALSE ');
15    END IF;
16   
17    IF P_OPERATOR = 'AND' THEN
18     DBMS_OUTPUT.PUT('AND ');
19     V_RESULT := P_IN1 AND P_IN2;
20    ELSIF P_OPERATOR = 'OR' THEN
21     DBMS_OUTPUT.PUT('OR ');
22     V_RESULT := P_IN1 OR P_IN2;
23    ELSE
24     RAISE_APPLICATION_ERROR('-20000', 'INPUT PARAMETER P_OPERATOR ERROR');
25    END IF;
26   
27    IF P_IN2 IS NULL THEN
28     DBMS_OUTPUT.PUT('NULL');
29    ELSIF P_IN2 THEN
30     DBMS_OUTPUT.PUT('TRUE');
31    ELSE
32     DBMS_OUTPUT.PUT('FALSE');
33    END IF;
34      
35    IF V_RESULT IS NULL THEN
36     DBMS_OUTPUT.PUT(':NULL');
37    ELSIF V_RESULT THEN
38     DBMS_OUTPUT.PUT(':TRUE');
39    ELSE
40     DBMS_OUTPUT.PUT(':FALSE');
41    END IF;
42    DBMS_OUTPUT.NEW_LINE;
43   END;
44  
45  BEGIN
46   V_BOOL1(1) := TRUE;
47   V_BOOL1(2) := FALSE;
48   V_BOOL1(3) := NULL;
49   V_BOOL2 := V_BOOL1;
50   FOR I IN 1..V_BOOL1.COUNT LOOP
51    FOR J IN 1..V_BOOL2.COUNT LOOP
52     P(V_BOOL1(I), V_BOOL2(J), 'AND');
53     P(V_BOOL1(I), V_BOOL2(J), 'OR');
54    END LOOP;
55   END LOOP;   
56  END;
57  /
TRUE AND TRUE:TRUE
TRUE OR TRUE:TRUE
TRUE AND FALSE:FALSE
TRUE OR FALSE:TRUE
TRUE AND NULL:NULL
TRUE OR NULL:TRUE
FALSE AND TRUE:FALSE
FALSE OR TRUE:TRUE
FALSE AND FALSE:FALSE
FALSE OR FALSE:FALSE
FALSE AND NULL:FALSE
FALSE OR NULL:NULL
NULL AND TRUE:NULL
NULL OR TRUE:TRUE
NULL AND FALSE:FALSE
NULL OR FALSE:NULL
NULL AND NULL:NULL
NULL OR NULL:NULL


        由于NULL是未知，所以NULL AND NULL、NULL OR NULL、NULL AND TRUE和NULL OR FALSE的值都是未知的，这些的结果仍然是NULL。
    那么为什么NULL AND FALSE和NULL OR TRUE得到了一个确定的结果呢？仍然从NULL的概念来考虑。NULL是未知的，但是目前NULL的类型是布尔类型，因此NULL只有可能是TRUE或者FALSE中的一个。
    而根据前面的表格，TRUE AND FALSE和FALSE AND FALSE的结果都是FALSE，也就是说不管NULL的值是TRUE还是FALSE，它与FALSE进行AND的结果一定是FALSE。
    同样的道理，TRUE AND TRUE和FALSE AND TRUE的结果都是TRUE，所以不管NULL取何值，NULL和TRUE的OR的结果都是TRUE。

AND操作图表变为：

AND	TRUE	FALSE	NULL
TRUE	TRUE	FALSE	NULL
FALSE	FALSE	FALSE	FALSE
NULL	NULL	FALSE	NULL

OR操作图表变为：

OR	TRUE	FALSE	NULL
TRUE	TRUE	TRUE	TRUE
FALSE	TRUE	FALSE	NULL
NULL	TRUE	NULL	NULL

最后，仍然来看一个例子：

 

SQL> SELECT * FROM TAB;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
PLAN_TABLE                     TABLE
T                              TABLE
T1                             TABLE
T2                             TABLE
T3                             TABLE
TEST                           TABLE
TEST1                          TABLE
TEST_CORRUPT                   TABLE
T_TIME                         TABLE
已选择9行。
SQL> SELECT * FROM TAB WHERE TNAME IN ('T', 'T1', NULL);
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
T                              TABLE
T1                             TABLE
SQL> SELECT * FROM TAB WHERE TNAME NOT IN ('T', 'T1', NULL);
未选定行
.

        对于IN和NOT IN与NULL的关系前面并没有说明，不过可以对其进行简单的变形：
TNAME IN (‘T’, ‘T1’, NULL) TNAME = ‘T’ OR TNAME = ‘T1’ OR TNAME = NULL
    根据前面的结果，当查询到T或T1这两条记录时，WHERE条件相当于TRUE AND FALSE AND NULL，其结果是TRUE，因此返回了两条记录。
TNAME NOT IN (‘T’, ‘T1’, NULL) TNAME != ‘T’ AND TNAME != ‘T1’ AND TNAME != NULL。
    WHERE条件相当于TRUE AND TRUE AND NULL，或TRUE AND FALSE AND NULL，其最终结果是NULL或者FALSE，所以，查询不会返回记录。

接着讨论一下NULL的布尔值运算NOT。
     对于TRUE和FALSE的NOT运算很简单，NOT TRUE=FALSE，NOT FALSE=TRUE，那么如果包含NULL的情况呢，首先还是用事实来说话

 SQL> SET SERVEROUT ON SIZE 100000
SQL> DECLARE
  2   TYPE T_BOOLEAN IS TABLE OF BOOLEAN INDEX BY BINARY_INTEGER;
  3   V_BOOL T_BOOLEAN;
  4  
  5   PROCEDURE P(P_IN BOOLEAN) AS
  6    V_RESULT BOOLEAN;
  7   BEGIN
  8    IF P_IN IS NULL THEN
  9     DBMS_OUTPUT.PUT('NOT NULL');
10    ELSIF P_IN THEN
11     DBMS_OUTPUT.PUT('NOT TRUE');
12    ELSE
13     DBMS_OUTPUT.PUT('NOT FALSE');
14    END IF;
15   
16    V_RESULT := NOT P_IN;
17   
18    IF V_RESULT IS NULL THEN
19     DBMS_OUTPUT.PUT(':NULL');
20    ELSIF V_RESULT THEN
21     DBMS_OUTPUT.PUT(':TRUE');
22    ELSE
23     DBMS_OUTPUT.PUT(':FALSE');
24    END IF;
25     DBMS_OUTPUT.NEW_LINE;
26    END;
27  
28  BEGIN
29   V_BOOL(1) := TRUE;
30   V_BOOL(2) := FALSE;
31   V_BOOL(3) := NULL;
32   FOR I IN 1..V_BOOL.COUNT LOOP
33     P(V_BOOL(I));
34   END LOOP;
35  END;
36  /
NOT TRUE:FALSE
NOT FALSE:TRUE
NOT NULL:NULL
PL/SQL 过程已成功完成。
        现在我们看到了一个很有趣的结果，NOT NULL的结果仍然是NULL。可能很多人对此并不理解。下面还是从NULL的基本概念来解释。
    NULL表示的是未知的含义，而增加一个NOT操作后，并不能使NULL变为一个确定的值，如果是TRUE，NOT TRUE将变为FALSE，如果是FALSE，NOT FALSE将变为TRUE，所有，即使进行了NOT操作，NULL本身的不确定性是仍然存在的。这就是最终结果仍然是NULL的原因。
    这里需要注意：这个NOT NULL是一个布尔操作，要和SQL中的NOT NULL约束进行区分。NOT NULL约束是一个定性的描述，只是表示列中的数据不允许为NULL。而这里的布尔操作，却是在进行求值，要得到对NULL取非的结果，所以仍然得到NULL。

NOT TRUE	NOT FALSE	NOT NULL
FALSE	TRUE	NULL

 关于NULL的字符串表示格式

         以前我总说空字符串’’等价于NULL，但是有些人喜欢钻牛角尖，所以我改一下说法，空字符串’’是NULL的字符类型的表现格式。
也许有人会认为，NULL就是NULL，本身没有类型的一说，但是我认为，NULL还是有类型的，只不过不同类型的NULL都用相同的关键字NULL来表示。而且，NULL本身也可以转化为任意类型的数据，因此给人的感觉是NULL没有数据类型。
其实NULL不但有数据类型，还有默认的数据类型，那就是字符类型。至于这个答案是如何推断出来的，请看：http://blog.csdn.net/mengxiangfeiyang/article/details/7974807

     不过上面说的这个默认的数据类型是在极限的情况下测试出来的，如果只是给出一个NULL，那么它是可以代表任意的类型的。
证明空字符串就是NULL是很容易的：
SQL> SELECT 1 FROM DUAL WHERE '' = '';
未选定行
SQL> SELECT 1 FROM DUAL WHERE '' IS NULL;
         1
----------
         1
SQL> SELECT DUMP(''), DUMP(NULL) FROM DUAL;
DUMP DUMP
---- ----
NULL NULL

上面三个SQL语句，任意一个都足以证明空字符串’’就是NULL。
有些人可能会说，既然’’就是NULL，为什么不能进行IS ’’的判断呢?

SQL> SELECT 1 FROM DUAL WHERE '' IS '';
SELECT 1 FROM DUAL WHERE '' IS ''
                               *
第 1 行出现错误:
ORA-00908: 缺失 NULL 关键字
其实从上面的错误信息就可以看到答案。原因就是IS NULL是Oracle的语法，在Oracle运行的时刻’’是NULL，但是现在Oracle还没有运行这句SQL，就由于语法不正确被SQL分析器挡住了。Oracle的语法并不包含IS ’’的写法，所以，这一点并不能称为’’不是NULL的理由。
那么我为什么还要说’’是NULL的字符表示形式呢？因为’’和NULL还确实不完全一样，对于NULL来说，它表示了各种数据类型的NULL值。而对于空字符串’’来说，虽然它也具有NULL的可以任意转化为其他任何数据类型的特点，但是无论是从形式上还是从本质上它都表现出了字符类型的特点。
下面通过一个例子来证明’’本质是字符类型的NULL。

SQL> CREATE OR REPLACE PACKAGE P_TEST_NULL AS
  2   FUNCTION F_RETURN (P_IN IN NUMBER) RETURN VARCHAR2;
  3   FUNCTION F_RETURN (P_IN IN VARCHAR2) RETURN VARCHAR2;
  4  END;
  5  /
程序包已创建。
SQL> CREATE OR REPLACE PACKAGE BODY P_TEST_NULL AS
  2  
  3   FUNCTION F_RETURN (P_IN IN NUMBER) RETURN VARCHAR2 AS
  4   BEGIN
  5    RETURN 'NUMBER';
  6   END;
  7   
  8   FUNCTION F_RETURN (P_IN IN VARCHAR2) RETURN VARCHAR2 AS
  9   BEGIN
10    RETURN 'VARCHAR2';
11   END;
12  
13  END;
14  /
程序包体已创建。
SQL> SELECT P_TEST_NULL.F_RETURN(3) FROM DUAL;
P_TEST_NULL.F_RETURN(3)
------------------------------------------------------------
NUMBER
SQL> SELECT P_TEST_NULL.F_RETURN('3') FROM DUAL;
P_TEST_NULL.F_RETURN('3')
------------------------------------------------------------
VARCHAR2
SQL> SELECT P_TEST_NULL.F_RETURN('') FROM DUAL;
P_TEST_NULL.F_RETURN('')
------------------------------------------------------------
VARCHAR2
SQL> SELECT P_TEST_NULL.F_RETURN(NULL) FROM DUAL;
SELECT P_TEST_NULL.F_RETURN(NULL) FROM DUAL
       *
第 1 行出现错误:
ORA-06553: PLS-307: 有太多的 'F_RETURN' 声明与此次调用相匹配

从这一点上可以看出’’实际上已经具备了数据类型。所以我将’’表述为空字符串是NULL的字符类型表现形式。
为什么空字符就是NULL

       试图解释为什么空字符就是NULL。而且准备简单描述一下字符串合并操作||的特殊性。
根据NULL的定义，NULL是不确定、未知的含义，那么为什么字符类型的NULL是一个空字符呢？而且，对于NULL的加、减、乘、除等操作的结果都是NULL，而为什么字符串合并操作||，当输入字符串有一个为空时，不会得到结果NULL。

SQL> SELECT NULL || 'A', 'B' || NULL, NULL || NULL FROM DUAL;
NU ' N
-- - -
A  B
上面两个问题需要从NULL的存储格式上解释。Oracle在存储数据时，先是存储这一列的长度，然后存储列数据本身。而对于NULL，只包含一个FF，没有数据部分。简单的说，Oracle用长度FF来表示NULL。
由于Oracle在处理的数据存储的时候尽量避免0的出现，因此，认为这里FF表示的是长度为0也是有一定道理的。或者从另一方面考虑，NULL只有一个长度，而没有数据部分。
而对于字符串来说，不管是长度为0的字符串还是没有任何数据的字符串，所代表的含义都是一个空字符串。从一点上讲，空字符串就是NULL也是有一定的道理的。
如果认为空字符串是字符形式的NULL，那么||操作的结果就不难理解了。
最后需要说明的是，不要将ORACLE里面的空字符串’’与C里面的空字符串””混淆。C里面的空字符串并非不不含任何数据，里面还包含了一个字符串结束符\0。C语言中的空字符串””对应Oracle中ASCII表中的0值，既CHR(0)。
但CHR(0)是一个确定的值，它显然不是NULL。

SQL> SELECT * FROM DUAL WHERE CHR(0) = CHR(0);
D
-
X
SQL> SELECT * FROM DUAL WHERE CHR(0) IS NULL;
未选定行
NULL和索引的关系

       如果说NULL类型已经比较容易出错了，那么索引问题就让NULL又一次成为问题的焦点。
大多数人都听说过这样一句话，索引不存储NULL值。这句话其实比不严谨。如果采用比较严谨的方式来说：B树索引不存储索引列全为空的记录。如果把这句话用在单列索引上，就是前面提到的B树索引不存储NULL。
首先索引分为BTREE和BITMAP两种，对于BTREE索引，是不存储NULL值的，而对于BITMAP索引，是存储NULL值的。
而从索引列的个数来划分，索引非为单列索引和复合索引，对于单列索引来说很简单，如果一条记录中这个索引字段为空，那么索引不会保存这条记录的信息。但是对于复合索引，由于存在着多个列，如果某一个索引列不为空，那么索引就会包括这条记录，即使其他所有的所有列都是NULL值。

 SQL> CREATE TABLE T AS SELECT * FROM DBA_OBJECTS;
表已创建。
SQL> DESC T
名称                                         是否为空? 类型
-------------------------------------------- -------- ------------------
OWNER                                                 VARCHAR2(30)
OBJECT_NAME                                           VARCHAR2(128)
SUBOBJECT_NAME                                        VARCHAR2(30)
OBJECT_ID                                             NUMBER
DATA_OBJECT_ID                                        NUMBER
OBJECT_TYPE                                           VARCHAR2(19)
CREATED                                               DATE
LAST_DDL_TIME                                         DATE
TIMESTAMP                                             VARCHAR2(19)
STATUS                                                VARCHAR2(7)
TEMPORARY                                             VARCHAR2(1)
GENERATED                                             VARCHAR2(1)
SECONDARY                                             VARCHAR2(1)
SQL> CREATE INDEX IND_T_OBJECT_ID ON T (OBJECT_ID);
索引已创建。
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T', CASCADE => TRUE)
PL/SQL 过程已成功完成。
SQL> SET AUTOT ON EXP
SQL> SELECT COUNT(*) FROM T;
  COUNT(*)
----------
     50297

执行计划
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    41   (3)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T    | 50297 |    41   (3)| 00:00:01 |
-------------------------------------------------------------------
SQL> SELECT /*+ INDEX(T IND_T_OBJECT_ID) */ COUNT(*) FROM T;
  COUNT(*)
----------
     50297

执行计划
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    41   (3)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T    | 50297 |    41   (3)| 00:00:01 |
-------------------------------------------------------------------

Oracle的优化器在确定是否使用索引的时候，第一标准是能否得到一个正确的结果。由于OBJECT_ID是可以为空的，而索引列不包含为空的记录。因此通过索引扫描无法得到一个正确的结果，这就是SELECT COUNT(*) FROM T不会使用OBJECT_ID上的索引的原因。
而对于BITMAP索引，则是另外的情况：
SQL> DROP INDEX IND_T_OBJECT_ID;
索引已删除。
SQL> CREATE BITMAP INDEX IND_B_T_DATA_ID ON T (DATA_OBJECT_ID);
索引已创建。
SQL> SELECT COUNT(*) FROM T;
  COUNT(*)
----------
     50297

执行计划
----------------------------------------------------------
Plan hash value: 3051411170
-------------------------------------------------------------------------
| Id  | Operation                | Name            | Rows  | Cost (%CPU)|
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                 |     1 |     2   (0)|
|   1 |  SORT AGGREGATE          |                 |     1 |            |
|   2 |   BITMAP CONVERSION COUNT|                 | 50297 |     2   (0)|
|   3 |    BITMAP INDEX FULL SCAN| IND_B_T_DATA_ID |       |            |
-------------------------------------------------------------------------
SQL> SELECT COUNT(*) FROM T WHERE DATA_OBJECT_ID IS NULL;
  COUNT(*)
----------
     46452

执行计划
----------------------------------------------------------
Plan hash value: 2587852253
-----------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                 |     1|     2|    2   (0)|
|   1 |  SORT AGGREGATE            |                 |     1|     2|           |
|   2 |   BITMAP CONVERSION COUNT   |                | 46452| 92904|    2   (0)|
|*  3 |    BITMAP INDEX SINGLE VALUE| IND_B_T_DATA_ID|      |      |           |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("DATA_OBJECT_ID" IS NULL)

      从上面的结果不难看出BITMAP索引中是包含NULL的。
      下面看看复合索引的情况：

SQL> DROP INDEX IND_B_T_DATA_ID;
索引已删除。
SQL> CREATE INDEX IND_T_OBJECT_DATA ON T(OBJECT_ID, DATA_OBJECT_ID);
索引已创建。
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T', METHOD_OPT => 'FOR ALL INDEXED COLUMNS')
PL/SQL 过程已成功完成。
SQL> SELECT OBJECT_ID, DATA_OBJECT_ID FROM T WHERE OBJECT_ID = 135;
OBJECT_ID DATA_OBJECT_ID
---------- --------------
       135

执行计划
----------------------------------------------------------
Plan hash value: 1726226519
---------------------------------------------------------------------------
| Id  | Operation        | Name              | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                   |     1 |     7 |     1   (0)|
|*  1 |  INDEX RANGE SCAN| IND_T_OBJECT_DATA |     1 |     7 |     1   (0)|
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("OBJECT_ID"=135)

     虽然结果中包含了NULL值，但是Oracle并没有读取表，而仅仅通过索引扫描就返回了结果，这说明复合索引中是可能包含NULL值的。
     本文简单说明了索引和NULL值的关系。这里并没有对反键索引（reverse）、逆序索引（desc）、函数索引（FBI）和CLUSTER索引进行说明。
     原因是这些索引其实都属于离不开BTREE索引和BITMAP索引的范畴。不必关心索引是否倒序或反键，只要是BTREE索引，就不会存储全NULL记录，反之，只要是BITMAP索引就会存储NULL值。
唯一需要注意的是函数索引，函数索引的真正索引列是函数的计算结果而不是行记录中的数据，清楚了这一点函数索引其实和普通索引就没有什么区别了。

     最后说明一下域索引。由于域索引的实现本身可能会很复杂，Oracle可能在内部是用一套表和过程来实现的，因此对于域索引是否存储NULL，要根据域索引的实现去进行具体的分析了。
NULL对SQL使用索引的影响

     观点一：判断一个列IS NOT NULL不会使用索引。
其实这个观点从一般意义上也解释不同，因为B树索引本身不存储键值全为NULL的记录，所以通过索引扫描得到的结果一定满足IS NOT NULL的要求。

SQL> CREATE TABLE T AS SELECT * FROM DBA_OBJECTS;
表已创建。
SQL> CREATE INDEX IND_T_DATAID ON T(DATA_OBJECT_ID);
索引已创建。
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T')
PL/SQL 过程已成功完成。
SQL> SET AUTOT TRACE
SQL> SELECT COUNT(*) FROM T WHERE DATA_OBJECT_ID IS NOT NULL;
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=2)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (FULL SCAN) OF 'IND_T_DATAID' (NON-UNIQUE) (Cost=26 Card=2946 Bytes=5892)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          5  consistent gets
          4  physical reads
          0  redo size
        377  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

由于索引的存储特性和IS NOT NULL访问本身没有冲突，因此，这种情况下很容易通过索引来得到相应的结果。
观点二：判断一个列IS NULL不会使用索引。
这里不讨论BITMAP索引。由于BITMAP索引保存NULL值，所以讨论BITMAP索引没有意义。这里仅讨论B树索引。


SQL> ALTER TABLE T MODIFY OWNER NOT NULL;
表已更改。
SQL> UPDATE T SET OBJECT_ID = NULL WHERE ROWNUM = 1;
已更新 1 行。
SQL> CREATE INDEX IND_T_OBJECT_OWNER ON T (OBJECT_ID, OWNER);
索引已创建。
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T', METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE 200')
PL/SQL 过程已成功完成。
SQL> SET AUTOT TRACE
SQL> SELECT * FROM T WHERE OBJECT_ID IS NULL;
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=93)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=3 Card=1 Bytes=93)
   2    1     INDEX (RANGE SCAN) OF 'IND_T_OBJECT_OWNER' (NON-UNIQUE) (Cost=2 Card=1)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
       1156  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
在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
CHECK约束中的NULL条件
SQL> CREATE TABLE T
  2  (
  3   ID NUMBER,
  4   FLAG CHAR(1) NOT NULL,
  5   CONSTRAINT CK_FLAG CHECK (FLAG IN ('A'))
  6  );
表已创建。
SQL> INSERT INTO T (ID, FLAG) VALUES (1, 'A');
已创建 1 行。
SQL> INSERT INTO T (ID, FLAG) VALUES (1, 'B');
INSERT INTO T (ID, FLAG) VALUES (1, 'B')
*
第 1 行出现错误:
ORA-02290: 违反检查约束条件 (YANGTK.CK_FLAG)

SQL> INSERT INTO T (ID, FLAG) VALUES (1, NULL);
INSERT INTO T (ID, FLAG) VALUES (1, NULL)
                                    *
第 1 行出现错误:
ORA-01400: 无法将 NULL 插入 ("YANGTK"."T"."FLAG")

由于FLAG列上的CHECK约束和NOT NULL约束，会导致除了’A’以外的数据都无法插入。但是如果在约束上添加一个NULL值：

SQL> ALTER TABLE T DROP CONSTRAINT CK_FLAG;
表已更改。
SQL> ALTER TABLE T ADD CONSTRAINT CK_FLAG CHECK (FLAG IN ('A', ''));
表已更改。

Oracle不会检查同一个列上的多个CHECK约束是否会发生冲突，因此上面添加FLAG IN NULL这个约束并不会报错。
但是这种添加的NULL，会导致这个CHECK约束失效：

SQL> INSERT INTO T (ID, FLAG) VALUES (1, 'A');
已创建 1 行。
SQL> INSERT INTO T (ID, FLAG) VALUES (1, 'B');
已创建 1 行。
SQL> INSERT INTO T (ID, FLAG) VALUES (1, 'C');
已创建 1 行。
SQL> INSERT INTO T (ID, FLAG) VALUES (1, NULL);
INSERT INTO T (ID, FLAG) VALUES (1, NULL)
                                    *
第 1 行出现错误:
ORA-01400: 无法将 NULL 插入 ("YANGTK"."T"."FLAG")

现在CHECK约束CK_FLAG已经完全失去作用，对于FLAG列除了NULL值外，其他任何值都可以插入。
CHECK的约束保证插入的数据不会导致CHECK检查的结果为假，CHECK结果为真和为空的记录都可以插入。
对于FLAG IN (‘A’, ‘’)的约束而言，任何记录的CHECK结果不是TRUE就是NULL。因此任何记录都是可以插入的