T-SQL FULL OUTER JOIN未返回关联表唯一值问题求助

嘿，这个坑我之前踩过！咱们先搞清楚为啥你的FULL OUTER JOIN变成了LEFT JOIN：

问题根源 #

你的WHERE A.Company = 'NVV'语句直接把FULL JOIN的效果废掉了！

FULL OUTER JOIN本来会返回：

• 两边匹配的行
• Actual表独有的行
• Budget表独有的行

但当Budget表有独有的行时，Actual表对应的A.Company是NULL，这时候WHERE A.Company = 'NVV'会把这些NULL的行直接过滤掉——相当于把Budget独有的数据全丢了，结果自然就和LEFT JOIN一模一样。

两种修正方法 #

方法1：提前过滤Actual表（更高效） #

先把Actual表中Company = 'NVV'的行筛选出来，再做FULL JOIN，同时用COALESCE处理NULL值，确保结果列的正确性：

SELECT 
    COALESCE(A.Company, APYY.Company) AS Company,
    COALESCE(A.segvalue1, APYY.segvalue1) AS segvalue1,
    COALESCE(A.segvalue2, APYY.segvalue2) AS segvalue2,
    APYY.BalanceAmtBYTD, 
    APYY.BalanceAmtBM, 
    SUM(CASE WHEN A.FiscalPeriod BETWEEN '1' AND '12' AND A.FiscalYear = '2017' THEN A.ActualsAmount ELSE NULL END) AS BalanceAmtCYTD, 
    SUM(CASE WHEN A.FiscalPeriod = '12' AND A.FiscalYear = '2017' THEN A.ActualsAmount ELSE NULL END) AS BalanceAmtCYM 
FROM (SELECT * FROM Actual WHERE Company = 'NVV') A 
FULL OUTER JOIN (
    SELECT B.Company, B.segvalue1, B.segvalue2, 
    SUM(CASE WHEN FiscalPeriod BETWEEN '1' AND '12' AND FiscalYear = '2017' THEN BudgetAmt ELSE NULL END) as BalanceAmtBYTD, 
    SUM(CASE WHEN FiscalPeriod = '12' AND FiscalYear = '2017' THEN BudgetAmt ELSE NULL END) as BalanceAmtBM 
    FROM Budget B 
    WHERE FiscalYear = '2017' AND BalanceType LIKE '%D%' 
    GROUP BY B.Company, B.segvalue1, B.segvalue2 
) APYY ON (A.Company = APYY.Company AND A.segvalue1 = APYY.segvalue1 AND A.segvalue2 = APYY.segvalue2) 
GROUP BY COALESCE(A.Company, APYY.Company), COALESCE(A.segvalue1, APYY.segvalue1), COALESCE(A.segvalue2, APYY.segvalue2), APYY.BalanceAmtBYTD, APYY.BalanceAmtBM

方法2：修改WHERE条件保留所有NVV行 #

用COALESCE合并两边的Company字段，确保无论哪边有值，只要是NVV就保留：

SELECT 
    COALESCE(A.Company, APYY.Company) AS Company,
    COALESCE(A.segvalue1, APYY.segvalue1) AS segvalue1,
    COALESCE(A.segvalue2, APYY.segvalue2) AS segvalue2,
    APYY.BalanceAmtBYTD, 
    APYY.BalanceAmtBM, 
    SUM(CASE WHEN A.FiscalPeriod BETWEEN '1' AND '12' AND A.FiscalYear = '2017' THEN A.ActualsAmount ELSE NULL END) AS BalanceAmtCYTD, 
    SUM(CASE WHEN A.FiscalPeriod = '12' AND A.FiscalYear = '2017' THEN A.ActualsAmount ELSE NULL END) AS BalanceAmtCYM 
FROM Actual A 
FULL OUTER JOIN (
    SELECT B.Company, B.segvalue1, B.segvalue2, 
    SUM(CASE WHEN FiscalPeriod BETWEEN '1' AND '12' AND FiscalYear = '2017' THEN BudgetAmt ELSE NULL END) as BalanceAmtBYTD, 
    SUM(CASE WHEN FiscalPeriod = '12' AND FiscalYear = '2017' THEN BudgetAmt ELSE NULL END) as BalanceAmtBM 
    FROM Budget B 
    WHERE FiscalYear = '2017' AND BalanceType LIKE '%D%' 
    GROUP BY B.Company, B.segvalue1, B.segvalue2 
) APYY ON (A.Company = APYY.Company AND A.segvalue1 = APYY.segvalue1 AND A.segvalue2 = APYY.segvalue2) 
WHERE COALESCE(A.Company, APYY.Company) = 'NVV'
GROUP BY COALESCE(A.Company, APYY.Company), COALESCE(A.segvalue1, APYY.segvalue1), COALESCE(A.segvalue2, APYY.segvalue2), APYY.BalanceAmtBYTD, APYY.BalanceAmtBM

额外说明 #

• 用COALESCE是为了避免结果中出现NULL的Company/segValue字段，因为当某一边没有匹配行时，对应的字段会是NULL，合并后就能显示正确的值。
• 方法1比方法2更高效，因为它提前过滤了Actual表的数据，减少了JOIN时的数据量。

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