SQL Server多表连接：无需SELECT DISTINCT如何获取唯一结果？

我有如下查询语句：

SELECT
    post_id, title, publish
FROM
    TABLE_A
    FULL OUTER JOIN TABLE_B ON ( TABLE_A.post_id = TABLE_B.post_id )
    LEFT OUTER JOIN TABLE_C ON ( TABLE_B.publish = TABLE_C.publish )

输出结果为：

post_id | title   | publish
P000001   TitleA    202308
P000002   TitleB    202308
P000003   TitleC    202308
P000004   TitleD    202308

之后我想要展示TABLE_A和TABLE_D中的company_id字段，于是修改查询为：

SELECT
    post_id, title, publish, company_id
FROM
    TABLE_A
    FULL OUTER JOIN TABLE_B ON ( TABLE_A.post_id = TABLE_B.post_id )
    LEFT OUTER JOIN TABLE_C ON ( TABLE_B.publish = TABLE_C.publish )
    LEFT OUTER JOIN TABLE_D ON ( TABLE_A.company_id = TABLE_D.company_id )

此时输出出现重复记录：

post_id | title   | publish | company_id
P000001   TitleA    202308    111
P000001   TitleA    202308    111
P000002   TitleB    202308    222
P000002   TitleB    202308    222
P000002   TitleB    202308    222
P000003   TitleC    202308    333
P000003   TitleC    202308    333
P000003   TitleC    202308    333
P000003   TitleC    202308    333
P000004   TitleD    202308    444

使用SELECT DISTINCT后得到了预期的唯一结果：

SELECT
    DISTINCT post_id, title, publish, company_id
FROM
    TABLE_A
    FULL OUTER JOIN TABLE_B ON ( TABLE_A.post_id = TABLE_B.post_id )
    LEFT OUTER JOIN TABLE_C ON ( TABLE_B.publish = TABLE_C.publish )
    LEFT OUTER JOIN TABLE_D ON ( TABLE_A.company_id = TABLE_D.company_id )

输出结果：

post_id | title   | publish | company_id
P000001   TitleA    202308    111
P000002   TitleB    202308    222
P000003   TitleC    202308    333
P000004   TitleD    202308    444

请问有没有办法不使用SELECT DISTINCT，而是通过调整JOIN方式或类似手段得到预期的唯一结果？

重复出现的核心原因是TABLE_C或TABLE_D中存在同一关联键对应多条记录的情况，JOIN时产生笛卡尔积导致重复行。可以通过以下几种方式避免使用DISTINCT：

1. 子查询提前去重关联表 #

如果TABLE_C/TABLE_D中同一关联键对应多条冗余记录，先在子查询中对这些表做去重，再进行JOIN：

SELECT
    post_id, title, publish, D.company_id
FROM
    TABLE_A
    FULL OUTER JOIN TABLE_B ON ( TABLE_A.post_id = TABLE_B.post_id )
    LEFT OUTER JOIN (SELECT DISTINCT publish FROM TABLE_C) AS C 
        ON ( TABLE_B.publish = C.publish )
    LEFT OUTER JOIN (SELECT DISTINCT company_id FROM TABLE_D) AS D 
        ON ( TABLE_A.company_id = D.company_id )

这种方式直接从关联源上消除一对多的情况，避免JOIN后产生重复。

2. 用聚合函数+分组确保唯一关联 #

如果需要保留TABLE_C/TABLE_D中的其他字段，可通过聚合函数（MAX()/MIN()等）配合GROUP BY，让每个关联键仅返回一条记录：

SELECT
    post_id, title, publish, D.company_id
FROM
    TABLE_A
    FULL OUTER JOIN TABLE_B ON ( TABLE_A.post_id = TABLE_B.post_id )
    LEFT OUTER JOIN (
        SELECT publish, MAX(optional_column) AS optional_column 
        FROM TABLE_C 
        GROUP BY publish
    ) AS C ON ( TABLE_B.publish = C.publish )
    LEFT OUTER JOIN (
        SELECT company_id, MAX(other_column) AS other_column 
        FROM TABLE_D 
        GROUP BY company_id
    ) AS D ON ( TABLE_A.company_id = D.company_id )

通过GROUP BY锁定关联键的唯一性，从根源上避免重复行生成。

3. 改用EXISTS替代JOIN（仅需判断关联存在时） #

如果只是需要确认TABLE_D中存在对应company_id，不需要返回TABLE_D的字段，可以用EXISTS子查询替代JOIN：

SELECT
    post_id, title, publish, TABLE_A.company_id
FROM
    TABLE_A
    FULL OUTER JOIN TABLE_B ON ( TABLE_A.post_id = TABLE_B.post_id )
    LEFT OUTER JOIN TABLE_C ON ( TABLE_B.publish = TABLE_C.publish )
WHERE EXISTS (
    SELECT 1 FROM TABLE_D 
    WHERE TABLE_D.company_id = TABLE_A.company_id
)

这种方式不会因为TABLE_D的多条记录产生重复，同时达到关联验证的目的。

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