NULL判断对SQL的影响

看到一条SQL，很具迷惑性，原始语句包含了业务属性，因此使用模拟的操作来复现这个问题。

创建两张测试表，要注意的是，object_id字段设置了NOT NULL，

SQL> create table a as select * from dba_objects;
Table created.


SQL> update a set object_id = 0 where object_id is null;
1 row updated.


SQL> commit;
Commit complete.


SQL> create table b as select * from a ;
Table created.


SQL> alter table a modify object_id not null;
Table altered.

原始的SQL，如下所示，可能有经验的朋友一下就看出来了问题，Oracle中判断字段是否为空应该使用is null或者is not null，使用任何其他的比较运算符，返回的都是false，

SQL> select /*+ test1 */ a.object_id, b.object_name 
     from a, b 
     where a.object_id = b.object_id and a.object_id = null;
no rows selected

他的执行计划，用的是排序合并连接，如果按照预估进行计算，从E-Rows能看出这两张表合并排序的预计行数是5330M行，应该不可能很快跑出来的，但实际上SQL很快就返回了，结果集是空，这点从A-Rows是0就可以得到证明。很重要的信息，就是在第一步，谓词信息显示filter(NULL IS NOT NULL AND NULL IS NOT NULL)，明显这是假命题，他的作用，其实就是告诉Oracle的优化器不用计算成本了(可参考《Oracle优化器的“短路”》)，

我们再进一步，看下这条SQL的10053，a.object_id=null被转换为了a.object_id=to_number(null) and b.object_id=to_number(null)，这里用到的是谓词的传递性(这是为什么filter中有两个NULL IS NOT NULL)，Oracle没将=null看作是对空值的判断，而将他作为一个普通的字符串处理的，由于字段object_id是number类型的，因此隐式转换to_number(null)，

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "A"."OBJECT_ID" "OBJECT_ID","B"."OBJECT_NAME" "OBJECT_NAME" FROM "BISAL"."A" "A","BISAL"."B" "B" 
WHERE "A"."OBJECT_ID"="B"."OBJECT_ID" AND "A"."OBJECT_ID"=TO_NUMBER(NULL) AND "B"."OBJECT_ID"=TO_NUMBER(NULL)

to_number(null)是什么？我们看下，返回的就是个NULL，因此用=和他进行比较的结果就是“未知”的，

SQL> select to_number(null), dump(to_number(null)) from dual;
TO_NUMBER(NULL) DUMP
--------------- ----
                NULL

我想说的是，之所以这条语句，很快返回0条，原因和《Oracle优化器的“短路”》是不同的，这里用的a.object_id = null，相当于是个错误的条件，用如下的示例，可以说明，我们使用is null检索object_id是空的记录会返回1条，但是用=null检索返回的就是0条，因为这个条件本身就是错的，自然是空，

SQL> select count(*) from dba_objects where object_id is null;
  COUNT(*)
----------
   1


SQL> select count(*) from dba_objects where object_id = null;
  COUNT(*)
----------
   0

如果用的is null判断，

SQL> select a.object_id, b.object_name from a, b where a.object_id = b.object_id and a.object_id is null;
no rows selected

如下执行计划，显示用的两表HASH JOIN，同样地，谓词条件出现了NULL IS NOT NULL，Oracle不会真正做A和B的全表扫描，避免了资源消耗，

10053显示，没做任何转换，is null就是个特殊条件，

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "A"."OBJECT_ID" "OBJECT_ID","B"."OBJECT_NAME" "OBJECT_NAME" FROM "BISAL"."A" "A","BISAL"."B" "B" 
WHERE "A"."OBJECT_ID"="B"."OBJECT_ID" AND "A"."OBJECT_ID" IS NULL

一方面说明Oracle的优化器很智能，能对这种肯定返回空的语句，施加特殊的条件，避免无用功，另一方面，我们在日常开发过程中，应该遵从一些规范避免出现=null这种判断的情况。

网上一些对NULL的描述说明，仅供参考，

1. Oracle认为NULL最大，因此排序时比其他数据都大。

2. nulls first：将NULL排在最前面。

select * from mytb order by mycol nulls first

3. null last：将NULL排在最后面。

select * from mytb order by mycol nulls last

4. 等价于没有任何值、是未知数。 5. NULL与0、空字符串、空格都不同。 6. 对空值做加、减、乘、除等运算操作，结果仍为空。 7. NULL的处理使用NVL函数或者NVL2。 8. 比较时使用关键字用“is null”和“is not null”，通过其他方式和任何值(包括NULL)的比较结果都是空。

9. 空值不能被索引，所以查询时有些符合条件的数据可能查不出来，count(*)中，用NVL(列名,0)处理后再查。