无primary key、foreign key关联两表匹配NULL值的SQL查询求解

问题根因 #

你编写的SQL存在两处错误：

1. 关联逻辑写错：原语句中T1.Code1 = T2.Code1 AND T1.Code1 = T2.Code2要求表1的Code1同时等于表2的Code1和Code2，仅yy/zzz这行数据满足这个错误条件，所以只会返回一条记录。
2. 未处理NULL匹配规则：SQL标准中NULL = NULL的判断结果为UNKNOWN，不会判定为匹配成功，所以Code1或Code2为NULL的行用普通等号关联会被漏掉。

修正后的SQL（兼容所有SQL Server版本） #

SELECT  
    T2.ID, T2.ColTest1, T2.ColTest2, T2.Code1, T2.Code2 
FROM 
    [dbo].[table2] T2 
INNER JOIN 
    [dbo].[table1] T1 
    ON -- 匹配Code1，同时兼容NULL场景
       (T1.Code1 = T2.Code1 OR (T1.Code1 IS NULL AND T2.Code1 IS NULL))
       AND -- 匹配Code2，同时兼容NULL场景
       (T1.Code2 = T2.Code2 OR (T1.Code2 IS NULL AND T2.Code2 IS NULL))
WHERE 
    T1.ISCode5 = 1

如果使用的是SQL Server 2022及以上版本，可以用更简洁的IS NOT DISTINCT FROM语法直接处理NULL值匹配：

SELECT  
    T2.ID, T2.ColTest1, T2.ColTest2, T2.Code1, T2.Code2 
FROM 
    [dbo].[table2] T2 
INNER JOIN 
    [dbo].[table1] T1 
    ON T1.Code1 IS NOT DISTINCT FROM T2.Code1
    AND T1.Code2 IS NOT DISTINCT FROM T2.Code2
WHERE 
    T1.ISCode5 = 1

以上语句会先匹配两表Code1、Code2完全一致（包括NULL状态一致）的记录，再筛选ISCode5=1的条目，最终得到你期望的输出结果。

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