mssql sqlserver 数据表存在null导致not in 子查询异常记录


摘要:
下文通过举例分析说明null导致not in查询异常的说明,如下所示:
实验环境:sql server 2008 R2


例:

      create table [maomao365.com_A]
	  (keyId int null,info varchar(30) null)

    create table [maomao365.com_B]
	  (keyId int null,info varchar(30) null)
   
    ---生成基础数据
     insert into [maomao365.com_A]
	 (keyId,info)values
	 (1,N'sql教程'),
	 (null,N'null空值陷阱判断'),
	 (2,N'null空值可能异常处理')
    
	  go 
     insert into [maomao365.com_B]
	 (keyId,info)values
	 (1,N'A'),
	 (null,N'B'),
	 (3,N'C')

      go
   
     ---以下语句未按预期输出 null 和keyId=2的值
     
     select  *  from  [maomao365.com_A] AS a
      where   a.keyId NOT IN 
	    (SELECT    b.keyId FROM 
	      [maomao365.com_B] AS b)
     go
    /*
     出现异常的原因为 b.keyId中存在为null的值
      则不会返回任何数据 
    */
    ---如果子查询表中存在null值,我们需采用以下写法
     select  *  from  [maomao365.com_A] AS a
      where   NOT EXISTS   
	    (SELECT    b.keyId FROM 
	      [maomao365.com_B] AS b where a.keyId =b.keyId)
	           
	 SELECT  a.*
	  FROM    [maomao365.com_A] AS a
        LEFT OUTER JOIN [maomao365.com_B] AS b ON a.keyId = b.keyId
      WHERE   b.keyId IS NULL
    
    
    
    go
	truncate table [maomao365.com_B]
	drop table [maomao365.com_B]
    truncate table [maomao365.com_A]
	drop table [maomao365.com_A]