SQL Server 在一个表中查找缺少的记录，其中列可能包含 NULL

Question

我有两个数据库Live和Dev。Dev数据库包含许多需要添加到实时数据库的记录，但两个数据库共享许多记录。MainspecialityCode、AppointmentType和ConsultantCode上有一个唯一的键。不存在所有三个都为空的记录，但记录存在于AppointmentType和Consultantcode

我尝试了以下方法：

SELECT [R_Dev].[ID]
,[R_Dev].[MainspecialityCode]
,[R_Dev].[Consultantcode]
,[R_Dev].[AppointmentType]
,[R_Dev].[GPCode]
,[R_Dev].[Process]
,[R_Dev].[Routing]
,[R_Dev].[CernerDocumentName]
,[R_Dev].[EmailAlert]
,[R_Dev].[ShowConsultant]
,[R_LIV].[MainspecialityCode]
,[R_LIV].[Consultantcode]
,[R_LIV].[AppointmentType]
FROM 
[STG1DOCDB10].[CalypsoPrint_Live].[dbo].[Cerner_Routing] R_Dev
LEFT JOIN  
[CalypsoPrint_Live].[dbo].[Cerner_Routing] R_LIV ON [R_LIV].[MainspecialityCode] = [R_Dev].[MainspecialityCode] 
AND [R_LIV].[Consultantcode] = [R_Dev].[Consultantcode] 
AND [R_LIV].[AppointmentType] = [R_Dev].AppointmentType
WHERE
[R_LIV].[MainspecialityCode] IS NULL
AND [R_LIV].[Consultantcode] IS NULL
AND [R_LIV].[AppointmentType] IS NULL

但是，我发现两者中的记录仍然出现在结果中。那么我如何在 Where 子句中进行比较，该子句仅列出 dev 中不在活动中的记录。

Answer 1

问题是您的NULL值无法通过LEFT JOIN。 因此，请对其进行修改以将其考虑在内：

FROM [STG1DOCDB10].[CalypsoPrint_Live].[dbo].[Cerner_Routing] R_Dev LEFT JOIN
[CalypsoPrint_Live].[dbo].[Cerner_Routing] R_LIV 
ON [R_LIV].[MainspecialityCode] = [R_Dev].[MainspecialityCode] And
([R_LIV].[Consultantcode] = [R_Dev].[Consultantcode] OR
[R_LIV].[Consultantcode] IS NULL AND [R_Dev].[Consultantcode] IS NULL
) AND
([R_LIV].[AppointmentType] = [R_Dev].AppointmentType OR
[R_LIV].[AppointmentType] IS NULL AND [R_Dev].AppointmentType IS NULL
)

您的WHERE子句只需要检查MainspecialityCode。