Oracle的where条件in/not in中包含NULL时的处理

最新推荐文章于 2023-06-19 11:35:10 发布

weixin_33827965

最新推荐文章于 2023-06-19 11:35:10 发布

阅读量955

点赞数 2

文章标签：数据库

原文链接：https://yq.aliyun.com/articles/529680

版权

我们在写SQL时经常会用到in条件，如果in包含的值都是非NULL值，那么没有特殊的，但是如果in中的值包含null值(比如in后面跟一个子查询，子查询返回的结果有NULL值)，Oracle又会怎么处理呢？

创建一个测试表t_in

 
         zx@TEST> 
         create  
         table  
         t_in(id number); 
        
         Table  
         created. 
        
         zx@TEST> 
         insert  
         into  
         t_in  
         values 
         (1); 
        
         1 row created. 
        
         zx@TEST> 
         insert  
         into  
         t_in  
         values 
         (2); 
        
         1 row created. 
        
         zx@TEST> 
         insert  
         into  
         t_in  
         values 
         (3); 
        
         1 row created. 
        
         zx@TEST> 
         insert  
         into  
         t_in  
         values 
         ( 
         null 
         ); 
        
         1 row created. 
        
         zx@TEST> 
         insert  
         into  
         t_in  
         values 
         (4); 
        
         1 row created. 
        
         zx@TEST> 
         commit 
         ; 
        
         Commit  
         complete. 
        
         zx@TEST> 
         select  
         *  
         from  
         t_in; 
        
         ID 
        
         ---------- 
        
         1 
        
         2 
        
         3 
        
         4

现在t_in表中有5条记录

１、in条件中不包含NULL的情况

 
         zx@TEST> 
         select  
         *  
         from  
         t_in  
         where  
         id  
         in  
         (1,3); 
        
         ID 
        
         ---------- 
        
         1 
        
         3 
        
         2  
         rows  
         selected.

上面的条件等价于id =1 or id = 3得到的结果正好是2；查看执行计划中可以看到 2 - filter("ID"=1 OR "ID"=3)说明我们前面的猜测是正确的

2、in条件包含NULL的情况

 
         zx@TEST> 
         select  
         *  
         from  
         t_in  
         where  
         id  
         in  
         (1,3, 
         null 
         ); 
        
         ID 
        
         ---------- 
        
         1 
        
         3 
        
         2  
         rows  
         selected.

上面的条件等价于id = 1 or id = 3 or id = null，我们来看下图当有id = null条件时Oracle如何处理

从上图可以看出当不管id值为NULL值或非NULL值，id = NULL的结果都是UNKNOWN，也相当于FALSE。所以上面的查结果只查出了1和3两条记录。

查看执行计划看到优化器对IN的改写

3、not in条件中不包含NULL值的情况

 
         zx@TEST> 
         select  
         *  
         from  
         t_in  
         where  
         id  
         not  
         in  
         (1,3); 
        
         ID 
        
         ---------- 
        
         2 
        
         4 
        
         2  
         rows  
         selected.

上面查询的where条件等价于id != 1 and id !=3，另外t_in表中有一行为null，它虽然满足!=1和!=3但根据上面的规则，NULL与其他值做=或!=比较结果都是UNKNOWN，所以也只查出了2和4。

从执行计划中看到优化器对IN的改写

4、not in条件中包含NULL值的情况

 
    
         zx@TEST> 
         select  
         *  
         from  
         t_in  
         where  
         id  
         not  
         in  
         (1,3, 
         null 
         ); 
        

            
        
 
         no  
         rows  
         selected 
        
 
  

上面查询的where条件等价于id!=1 and id!=3 and id!=null，根据上面的规则，NULL与其他值做=或!=比较结果都是UNKNOWN，所以整个条件就相当于FALSE的，最终没有查出数据。

从执行计划中查看优化器对IN的改写

总结一下，使用in做条件时时始终查不到目标列包含NULL值的行，如果not in条件中包含null值，则不会返回任何结果，包含in中含有子查询。所以在实际的工作中一定要注意not in里包含的子查询是否包含null值。

 
    
         zx@TEST> 
         select  
         *  
         from  
         t_in  
         where  
         id  
         not  
         in  
         ( 
         select  
         id  
         from  
         t_in  
         where  
         id = 1  
         or  
         id  
         is  
         null 
         ); 
        

            
        
 
         no  
         rows  
         selected 
        
 
  