tsql - 完全外部联接不返回所有行？

Question

我有一个表，其中包含多个日期的多条记录。

我试图查看“日期 1”和“日期 2”之间的区别，但我的完整外部联接没有返回我期望的数据。

我知道日期 1 有 13278 行，日期 2 有 13282 行 - 因此我希望看到至少 13282 行，但我返回 13195...这是一个 INNER JOIN (我对此进行了测试)。

我希望得到这样的结果:

001     000123    009    NULL    1000
001     000124    009    1000    1000
001     000125    009    1000    1000
001     000126    009    1000    NULL

但这不会从任何一侧获取任何空行？

SELECT 
    COALESCE(c.AccountBranch, p.AccountBranch)
    , COALESCE(c.AccountNumber, p.AccountNumber)
    , COALESCE(c.AccountSuffix, p.AccountSuffix)
    , c.PrincipleAmount
    , p.PrincipleAmount
    FROM ADStaging..cb_account_extension_principle_dpd c
    FULL OUTER JOIN ADStaging..cb_account_extension_principle_dpd p
        ON p.AccountBranch = c.AccountBranch
        AND p.AccountNumber = c.AccountNumber
        AND p.AccountSuffix = c.AccountSuffix
WHERE 
    (c.BusinessDataDate IS NULL OR c.BusinessDataDate = @CurrentBusinessDataDate)
    AND
    (p.BusinessDataDate IS NULL OR p.BusinessDataDate = @PreviousBusinessDataDate)

---

这行得通吗 - 将联接的“键”组合到两个单独的选择语句中？

SELECT
      COALESCE(C.Account, P.Account) AS Account
    , COALESCE(C.AccountBranch, P.AccountBranch) as AccountBranch
    , COALESCE(C.AccountNumber, P.AccountNumber) as AccountNumber
    , COALESCE(C.AccountSuffix, P.AccountSuffix) as AccountSuffix
    , P.PrincipleAmount AS PreviousAmount
    , C.PrincipleAmount AS CurrentAmount
    , ISNULL(C.PrincipleAmount, P.PrincipleAmount) - ISNULL(P.PrincipleAmount,0)
FROM 
(SELECT 
    (pd.AccountBranch + pd.AccountNumber + pd.AccountSuffix) AS Account
    , pd.AccountBranch
    , pd.AccountNumber
    , pd.AccountSuffix
    , pd.PrincipleAmount
FROM ADStaging..cb_account_extension_principle_dpd pd
WHERE pd.BusinessDataDate = @CurrentBusinessDataDate) C
FULL OUTER JOIN 
(SELECT 
    (pd.AccountBranch + pd.AccountNumber + pd.AccountSuffix) AS Account
    , pd.AccountBranch
    , pd.AccountNumber
    , pd.AccountSuffix
    , pd.PrincipleAmount
FROM ADStaging..cb_account_extension_principle_dpd pd
WHERE pd.BusinessDataDate = @PreviousBusinessDataDate) P
    ON P.Account = C.Account
WHERE 
    (P.PrincipleAmount IS NULL OR C.PrincipleAmount IS NULL)
    OR
    P.PrincipleAmount <> C.PrincipleAmount

---

但这不是 - 加入组合值 - 仅当它们是单独的表时？

SELECT 
    COALESCE(c.AccountBranch, p.AccountBranch)
    , COALESCE(c.AccountNumber, p.AccountNumber)
    , COALESCE(c.AccountSuffix, p.AccountSuffix)
    , c.PrincipleAmount
    , p.PrincipleAmount
    FROM ADStaging..cb_account_extension_principle_dpd c
    FULL OUTER JOIN ADStaging..cb_account_extension_principle_dpd p
        ON (p.AccountBranch + p.AccountNumber + p.AccountSuffix)
        = (c.AccountBranch + c.AccountNumber + c.AccountSuffix)
WHERE 
    (c.BusinessDataDate = @CurrentBusinessDataDate)
    AND
    (p.BusinessDataDate = @PreviousBusinessDataDate)

Answer 1

where 正在杀死外部连接。
列不能同时为 null 和 = 值。
将条件放入连接中。

SELECT COALESCE(c.AccountBranch, p.AccountBranch)
     , COALESCE(c.AccountNumber, p.AccountNumber)
     , COALESCE(c.AccountSuffix, p.AccountSuffix)
     , c.PrincipleAmount, p.PrincipleAmount
FROM            cb_account_extension_principle_dpd c
FULL OUTER JOIN cb_account_extension_principle_dpd p
  ON p.AccountBranch = c.AccountBranch
 AND p.AccountNumber = c.AccountNumber 
 AND p.AccountSuffix = c.AccountSuffix
 AND c.BusinessDataDate = @CurrentBusinessDataDate
 AND p.BusinessDataDate = @PreviousBusinessDataDate