SQL Server中关联列含NULL时多列关联的解决方法

在SQL Server中对两张表进行多列关联时，因ProviderID列存在NULL值，导致关联结果中部分记录的City_Name、State_Name、Hospital_Name字段异常显示为NULL，不符合预期。

现有数据集 #

主表#df #

CREATE TABLE #df 
(
    CityID bigint,
    StateID VARCHAR (5),
    HospitalID bigint,
    ProviderID BIGINT,
    PT_Count BIGINT 
);

INSERT INTO #df (CityID, StateID, HospitalID, ProviderID, PT_Count)
VALUES
(70289, 'WY', 10001, 34523, 300),
(70500, 'AZ', 15000, NULL ,200),
(34523, 'NM', 54634, 78543, 100),
(90010, 'CA', 65738, NULL, 500)

关联表#df2 #

CREATE TABLE #df2 
(
    CityID bigint,
    City_Name VARCHAR(20),
    StateID VARCHAR (5),
    State_Name VARCHAR(20),
    HospitalID bigint,
    Hospital_Name VARCHAR(20),
    ProviderID BIGINT,
    Provider_Name VARCHAR(20)
);

INSERT INTO #df2 (CityID, City_Name, StateID, State_Name, HospitalID, Hospital_Name, ProviderID, Provider_Name)
VALUES
(70289, 'Cheyenne', 'WY', 'Wyoming', '10001', 'Wyoming Gen', 34523, 'Dr.Joe'),
(70500, 'Phoenix', 'AZ', 'Arizona', 15000, 'Arizona Gen', NULL, NULL),
(34523, 'Santa Fe', 'NM', 'New Mexico', '54634', 'NM Gen',78543, 'Dr. Jim'),
(90010, 'Beverly Hills', 'CA', 'California', 65738, 'Bev Gen', NULL, NULL)

问题查询语句 #

执行以下查询时，ProviderID为NULL的记录对应的City_Name、State_Name、Hospital_Name均返回NULL：

SELECT a.CityID
     , b.City_Name
     , a.StateID
     , b.State_Name
     , a.HospitalID
     , b.Hospital_Name
     , a.ProviderID
     , b.Provider_Name
     , a.PT_Count
FROM #df a
LEFT JOIN #df2 b ON b.CityID = a.CityID 
                 AND b.HospitalID = a.HospitalID 
                 AND b.ProviderID = a.ProviderID 
                 AND b.StateID = a.StateID

问题原因 #

SQL中NULL不等于任何值，包括NULL本身，当a.ProviderID为NULL时，b.ProviderID = a.ProviderID这个条件永远无法成立，导致无法匹配到#df2中对应的记录，进而使关联字段返回NULL。

修复方案 #

方案一：显式判断NULL相等 #

修改关联条件，当两边ProviderID均为NULL时视为匹配：

SELECT a.CityID
     , b.City_Name
     , a.StateID
     , b.State_Name
     , a.HospitalID
     , b.Hospital_Name
     , a.ProviderID
     , b.Provider_Name
     , a.PT_Count
FROM #df a
LEFT JOIN #df2 b ON b.CityID = a.CityID 
                 AND b.HospitalID = a.HospitalID 
                 AND b.StateID = a.StateID
                 AND (b.ProviderID = a.ProviderID OR (b.ProviderID IS NULL AND a.ProviderID IS NULL))

方案二：用占位值替换NULL后比较 #

使用ISNULL函数将NULL替换为一个不会出现在实际数据中的值（比如-1，假设ProviderID均为正整数），再进行比较：

SELECT a.CityID
     , b.City_Name
     , a.StateID
     , b.State_Name
     , a.HospitalID
     , b.Hospital_Name
     , a.ProviderID
     , b.Provider_Name
     , a.PT_Count
FROM #df a
LEFT JOIN #df2 b ON b.CityID = a.CityID 
                 AND b.HospitalID = a.HospitalID 
                 AND b.StateID = a.StateID
                 AND ISNULL(b.ProviderID, -1) = ISNULL(a.ProviderID, -1)

两种方案都能正确匹配ProviderID为NULL的记录，返回预期的City_Name、State_Name和Hospital_Name值。

内容的提问来源于stack exchange，提问作者Trevor M