多连接SQL查询无匹配时显示Null值的实现方法

我的SQL查询在MaterialCodes表中不存在匹配传入vendorId的物料编码时，不会返回任何行。如何让MaterialCode列在无匹配时显示Null？以下是我的查询：

SELECT 
    Materials.Id, Materials.Type, Materials.Name, Materials.Thickness, 
    Materials.Width, Materials.Length, Materials.Grain, MaterialCodes.Code
FROM 
    dbo.HFCMaterialsDVI AS DVIMaterials
INNER JOIN 
    dbo.HFCMaterials AS Materials ON DVIMaterials.Id = Materials.ParentId
INNER JOIN 
    dbo.HFCMaterialsCode as MaterialCodes ON Materials.Id = MaterialCodes.MaterialId
WHERE 
    DVIMaterials.Name = 'Fashionista' 
    AND MaterialCodes.VendorId = 2

解决方案 #

将与MaterialCodes表的INNER JOIN替换为LEFT JOIN，并把MaterialCodes.VendorId = 2的条件从WHERE子句移至JOIN的ON条件中，即可实现无匹配时MaterialCodes.Code显示为Null，同时保留物料的其他信息。

修改后的SQL如下：

SELECT 
    Materials.Id, Materials.Type, Materials.Name, Materials.Thickness, 
    Materials.Width, Materials.Length, Materials.Grain, MaterialCodes.Code
FROM 
    dbo.HFCMaterialsDVI AS DVIMaterials
INNER JOIN 
    dbo.HFCMaterials AS Materials ON DVIMaterials.Id = Materials.ParentId
LEFT JOIN 
    dbo.HFCMaterialsCode as MaterialCodes 
        ON Materials.Id = MaterialCodes.MaterialId
        AND MaterialCodes.VendorId = 2
WHERE 
    DVIMaterials.Name = 'Fashionista'

关键说明 #

• LEFT JOIN特性：会保留左表（这里是通过INNER JOIN关联后的DVIMaterials和Materials结果集）的所有行，当右表（MaterialCodes）无匹配记录时，右表对应字段自动填充为Null。
• 条件位置调整：如果将MaterialCodes.VendorId = 2放在WHERE子句中，会过滤掉所有MaterialCodes字段为Null的行，效果等同于INNER JOIN；只有将其作为LEFT JOIN的关联条件，才能在关联时就筛选VendorId，同时保留无匹配的物料行。

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