FULL OUTER JOIN表现为INNER JOIN：SQL查询未返回预期记录

预期以下SQL查询返回3条记录，但实际仅返回2条，缺少Description Id=21的记录——该行所有以“part”开头的列应为NULL。已返回的两条记录是正确的：FULL OUTER JOIN能正确合并零件与类别同Code的描述行（Description Ids 18和20），但整体表现类似INNER JOIN，未包含零件类别有描述但零件无对应Code（9）的记录。

核心需求：列出零件的所有相关描述（直接关联或通过类别间接关联），当零件和类别有相同Code的描述时，合并为一行显示。

表结构与测试数据 #

create table Temp.Part (
    Id int PRIMARY KEY CLUSTERED (Id asc),
    CategoryId int not null
)

create table Temp.Category (
    Id int PRIMARY KEY CLUSTERED (Id asc)
)

create table Temp.Description (
    Id int PRIMARY KEY CLUSTERED (Id asc),
    Code int not null,
    Value nvarchar(200) not null
)

create table Temp.DescriptionLink (
    Id int PRIMARY KEY CLUSTERED (Id asc),
    EntityId int not null,
    DescriptionId int not null
)
go

declare @partId int = 1
declare @categoryId int = 15

insert into Temp.Category values (@categoryId)
insert into Temp.Part values (@partId, @categoryId)

-- 零件的直接描述
insert into Temp.Description values (17, 6, 'This should output: desc #1 on Part')
insert into Temp.DescriptionLink values (50, @partId, 17)

insert into Temp.Description values (18, 4, 'This should output: desc #2 on Part (common)')
insert into Temp.DescriptionLink values (51, @partId, 18)

 -- 零件所属类别的描述
insert into Temp.Description values (20, 4, 'This should output: desc #1 on Category (common)')
insert into Temp.DescriptionLink values (70, @categoryId, 20)

insert into Temp.Description values (21, 9, 'This should output: desc #2 on Category')
insert into Temp.DescriptionLink values (71, @categoryId, 21)

-- 与零件无关的描述（不应出现在结果中）
insert into Temp.DescriptionLink values (72, -4, 99)
insert into Temp.Description values (99, 9, 'This should not be in the output because it belongs to a category not assigned to the part')
go

原查询的问题分析 #

原查询中FULL OUTER JOIN的位置错误，导致类别独有的描述行被WHERE Part.Id=1过滤：

1. 原查询连接顺序为：((Category JOIN Part) LEFT JOIN partDesc) FULL OUTER JOIN catDesc
2. 当类别存在Code=9的描述且无对应零件描述时，FULL OUTER JOIN会生成一行左侧（包含Part、Category、partDesc的列）全为NULL的记录
3. 该行的Part.Id为NULL，被WHERE Part.Id=1条件过滤，最终未出现在结果中

修正后的查询 #

通过先获取所有关联的Code（零件的Code + 类别的Code），再分别关联零件和类别的描述，确保所有相关Code的行都被保留：

select  
    partDesc.DescriptionId 'Part Desc Id', 
    catDesc.DescriptionId 'Category''s Description Id',
    partDesc.Value 'part''s desc', 
    catDesc.Value 'Category''s Desc',
    coalesce(partDesc.Code, catDesc.Code) 'DescriptionCode'
from
    Temp.Part part
    inner join Temp.Category cat on part.CategoryId = cat.Id
    -- 获取零件和类别所有关联的Code（去重）
    left join (
        select Code 
        from Temp.DescriptionLink link 
        inner join Temp.Description descr on descr.Id = link.DescriptionId 
        where link.EntityId = part.Id
        
        union
        
        select Code 
        from Temp.DescriptionLink link 
        inner join Temp.Description descr on descr.Id = link.DescriptionId 
        where link.EntityId = cat.Id
    ) allCodes on 1=1
    -- 关联零件描述
    left join (
        select  link.entityId 'PartId', descr.Code, descr.Id 'DescriptionId', descr.Value
        from Temp.DescriptionLink link 
        inner join Temp.Description descr on descr.Id = link.DescriptionId
    ) partDesc on partDesc.PartId = part.Id and partDesc.Code = allCodes.Code
    -- 关联类别描述
    left join (
        select  link.entityId 'CategoryId', descr.Code, descr.Id 'DescriptionId', descr.Value
        from Temp.DescriptionLink link 
        inner join Temp.Description descr on descr.Id = link.DescriptionId
    ) catDesc on catDesc.CategoryId = cat.Id and catDesc.Code = allCodes.Code
where
    part.Id = 1

结果验证 #

修正后的查询会返回3条符合预期的记录：

1. Part Desc Id=17，Category's Description Id=NULL（零件独有的Code=6）
2. Part Desc Id=18，Category's Description Id=20（零件和类别共有的Code=4）
3. Part Desc Id=NULL，Category's Description Id=21（类别独有的Code=9）

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