改写SQL查询：避免WHERE子句干扰RIGHT OUTER JOIN结果

问题根源 #

你的所有连接类型结果都一样，核心是外层WHERE子句的过滤条件抵消了外连接的作用：

• 用RIGHT OUTER JOIN时，r1会出现ItemID为NULL的行（对应r2有但r1没有的商品），但WHERE里的r1.ItemID NOT IN (SELECT ItemID FROM DM.C)会直接把这些NULL行过滤掉——因为NULL和任何值比较的结果都是UNKNOWN，不满足NOT IN的条件。
• 同理，LEFT OUTER JOIN时r2.ItemID的NULL行也会被r2.ItemID NOT IN (...)过滤；FULL OUTER JOIN时两边的NULL行都会被筛掉，最后结果就和内连接完全一致。

解决方案 #

有两种靠谱的改写方式，按需选：

方式1：把过滤条件移到子查询里（推荐） #

把排除DM.C的逻辑直接放到r1和r2的子查询中，聚合前就过滤掉不需要的ItemID，外连接后不会再被额外过滤：

DECLARE @BranchC VARCHAR(500) = '01001'
DECLARE @Range1 VARCHAR(255) = '202101'
DECLARE @Range2 VARCHAR(255) = '202201'

SELECT
    sub1.*
FROM
    (
        SELECT
            ISNULL(r1.ItemID, r2.ItemID) AS ItemID,
            r2.TotalItemPrice as R2_ItemPrice,
            r1.TotalItemPrice AS R1_ItemPrice
        FROM
            (
                SELECT 
                    ItemID,
                    SUM(TotalItemPrice) AS TotalItemPrice
                FROM
                    DM.M
                WHERE
                    InvoiceYearMonth IN (SELECT VALUE FROM dbo.SplitString(@Range1,','))
                    AND BranchCode IN (SELECT ITEM FROM dbo.DelimitedSplit8K(@BranchC,N','))
                    AND ItemID NOT IN (SELECT ItemID FROM DM.C) -- 移到子查询提前过滤
                GROUP BY
                    ItemID
            ) r1
            RIGHT OUTER JOIN -- 改为右外连接
            (
                SELECT 
                    ItemID,
                    SUM(TotalItemPrice) AS TotalItemPrice
                FROM
                    DM.M
                WHERE
                    InvoiceYearMonth IN (SELECT VALUE FROM dbo.SplitString(@Range2,','))
                    AND BranchCode IN (SELECT ITEM FROM dbo.DelimitedSplit8K(@BranchC,N','))
                    AND ItemID NOT IN (SELECT ItemID FROM DM.C) -- 移到子查询提前过滤
                GROUP BY
                    ItemID
            ) r2
            ON r1.ItemID = r2.ItemID
        -- 外层过滤条件已移除，因为子查询已经处理
    ) sub1

方式2：修改外层WHERE条件，兼容NULL值 #

如果要保留外层过滤逻辑，就得针对外连接产生的NULL值做特殊处理，确保这些行不会被过滤：

DECLARE @BranchC VARCHAR(500) = '01001'
DECLARE @Range1 VARCHAR(255) = '202101'
DECLARE @Range2 VARCHAR(255) = '202201'

SELECT
    sub1.*
FROM
    (
        SELECT
            ISNULL(r1.ItemID, r2.ItemID) AS ItemID,
            r2.TotalItemPrice as R2_ItemPrice,
            r1.TotalItemPrice AS R1_ItemPrice
        FROM
            (
                SELECT 
                    ItemID,
                    SUM(TotalItemPrice) AS TotalItemPrice
                FROM
                    DM.M
                WHERE
                    InvoiceYearMonth IN (SELECT VALUE FROM dbo.SplitString(@Range1,','))
                    AND BranchCode IN (SELECT ITEM FROM dbo.DelimitedSplit8K(@BranchC,N','))
                GROUP BY
                    ItemID
            ) r1
            RIGHT OUTER JOIN -- 改为右外连接
            (
                SELECT 
                    ItemID,
                    SUM(TotalItemPrice) AS TotalItemPrice
                FROM
                    DM.M
                WHERE
                    InvoiceYearMonth IN (SELECT VALUE FROM dbo.SplitString(@Range2,','))
                    AND BranchCode IN (SELECT ITEM FROM dbo.DelimitedSplit8K(@BranchC,N','))
                GROUP BY
                    ItemID
            ) r2
            ON r1.ItemID = r2.ItemID
        WHERE
            (r1.ItemID IS NULL OR r1.ItemID NOT IN (SELECT ItemID FROM DM.C)) -- 兼容r1的NULL行
            AND r2.ItemID NOT IN (SELECT ItemID FROM DM.C) -- 右外连接下r2不会为NULL
    ) sub1

效果说明 #

• 方式1执行后，RIGHT OUTER JOIN会返回r2中所有符合条件的ItemID，不管r1里有没有对应记录（r1不存在时R1_ItemPrice显示NULL）。
• 方式2通过r1.ItemID IS NULL OR ...保留了右外连接产生的r1为NULL的行，不会被过滤掉。

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