NULL判断对SQL的影响

素颜马尾好姑娘i 2022-10-14 13:55 10阅读 0赞

看到一条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优化器的“短路”][Oracle]》)，

![8501d789323318fc24c0d77407e8b571.png][]

我们再进一步，看下这条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优化器的“短路”][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的全表扫描，避免了资源消耗，

![7046a87209d78df369ad854a5f87e20b.png][]

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)处理后再查。

**近期更新的文章：**

《[什么是快乐星球？什么又是“标量子查询”呢？][Link 1]》

《[VMWare增加磁盘空间的操作][VMWare]》

《[曾经运维生涯中的几个“最”][Link 2]》

《[一条半连接SQL的优化过程][SQL]》

《[如何找到隐式转换的SQL？][SQL 1]》

《[为什么日期不建议使用VARCHAR2或者NUMBER？][VARCHAR2_NUMBER]》

《[查看时间戳类型的绑定变量][Link 3]》

《[写代码如何事半功倍？][Link 4]》

《[支持超过4000字节的varchar2类型][4000_varchar2]》

**文章分类和索引：**

《[公众号700篇文章分类和索引][700]》

[Oracle]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a48fddf52d9999788838c494741b2d3f73b0b30e9cf3425c63ddfad149739e8ccaaf66d9&idx=1&mid=2247491964&scene=21&sn=01531283836cc00f996bd199bf9dd541#wechat_redirect
[8501d789323318fc24c0d77407e8b571.png]: /images/20221014/446beec6b6494d8fafce79245e381521.png
[7046a87209d78df369ad854a5f87e20b.png]: /images/20221014/455f02ca18dc46a4b8792b51c7f4bf0e.png
[Link 1]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a7deddf52ec8a050d5d9acaf8f707289707b5530b7b1eb27b2e4577cea179b989b43d60a&idx=1&mid=2247492141&scene=21&sn=540622bdb1c79b8ce62f75ae70c827da#wechat_redirect
[VMWare]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a7e8ddf52efe6fd6ca95fc4405d1bdeff10c528b571824fed58a57b11063c0960066d675&idx=1&mid=2247492123&scene=21&sn=518d5e367e0dbd5486f2d29745b41012#wechat_redirect
[Link 2]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a7f2ddf52ee49775b374898978d2f13624f1e51dcbfecdfdd32925332132e4eaa88cc7bd&idx=1&mid=2247492097&scene=21&sn=f2c338461919594062136eb10a70e9c4#wechat_redirect
[SQL]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a404ddf52d127b63d6a15e3d204db28426064a005e58ea83a6b89f30256eab76d464aad2&idx=1&mid=2247492087&scene=21&sn=743c7ae9ad0d77073f291ae5abc2ef29#wechat_redirect
[SQL 1]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a414ddf52d02b74d3e476d6c4daf1aa9ba6a5be958a4f19d9b880a0029b43895eebceb2f&idx=1&mid=2247492071&scene=21&sn=05655f3f6ec05fd54b94097552708259#wechat_redirect
[VARCHAR2_NUMBER]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a411ddf52d07968c523b8869089f79e531474503306a0561ad1be8ed36625ef8ad8d3b4a&idx=1&mid=2247492066&scene=21&sn=06e18747abd40914a505c0c62da889c0#wechat_redirect
[Link 3]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a421ddf52d377a89c56aaadf746d892ae18c21d70b31c9f150b2b0c81483be5c841c7f71&idx=1&mid=2247492050&scene=21&sn=2bcfb42048e4d1ddf4f7eafa5523b7f2#wechat_redirect
[Link 4]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a432ddf52d2475aa2c81573af45a075b055cc1d4171c4036b73bc8c9a0648f2ea2952904&idx=1&mid=2247492033&scene=21&sn=d471b5da9c7c18e065fe38b299737db6#wechat_redirect
[4000_varchar2]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a449ddf52d5feea2ac1b64a23f9214b1f939e84e5360dc857deeb7dadff780ad488a1c9c&idx=1&mid=2247492026&scene=21&sn=f31d0e69ea77eb0b33ff32da933a3fe6#wechat_redirect
[700]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a5dfddf52cc949a5b8b316ee905927ff399713b34aec7d806c656e8ada09bfb562c039ca&idx=1&mid=2247491628&scene=21&sn=83bd320bd28da84501fada2e8d515e77#wechat_redirect