SQL中not in有Null值时

CREATE TABLE [dbo].[Table_A](
 [ID] [nchar](10) NULL,
 [Name] [nchar](10) NULL
) ON [PRIMARY]

GO

ID         Name
001        张三        
002        李四        
003        王五              

 

CREATE TABLE [dbo].[Table_B](
 [ID] [nchar](10) NULL,
 [Name] [nchar](10) NULL
) ON [PRIMARY]

GO

ID       Name
NULL     张三        
002      李四        
NULL     王五 

		

SELECT  *
FROM    dbo.Table_A AS a
WHERE   a.ID NOT IN ( SELECT    b.ID
                      FROM      dbo.Table_B AS b)

		

SELECT *
FROM dbo.TableA AS a
WHERE a.id NOT IN ( 2, NULL )


--等同于：

SELECT *
FROM Table_A AS a
WHERE a.id <> 2
 AND a.ID <> NULL

--由于NULL值不能参与比较运算符，导致条件不成立，查询不出来数据。

		

--写法1                      
SELECT  *
FROM    dbo.Table_A AS a
WHERE   a.ID NOT IN ( SELECT    b.ID
                      FROM      dbo.Table_B AS b
                      WHERE     b.ID IS NOT NULL ) --排除NULL值参与运算符比较


--写法2                                                        
SELECT  *
FROM    dbo.Table_A AS a
WHERE   NOT EXISTS ( SELECT *
                     FROM   dbo.Table_B AS b
                     WHERE  a.ID = b.ID )