完全外部联接不返回所有行? [英] Full outer join not returning all rows?

问题描述

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

我试图查看日期1"和日期2"之间的区别，并且我的完整外部联接未返回我期望的数据.

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

我希望得到这样的结果:

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)

解决方案

在哪里杀死外部联接.
列不能同时为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

I have a table that contains multiple records for multiple dates.

I am trying to see the difference between "date 1" and "date 2" and my full outer join is not returning the data I was expecting.

I know there are 13278 rows on date 1 and 13282 on date 2 - therefore I would expect to see at least 13282 rows, but I get back 13195...which is an INNER JOIN (I tested this).

I am hoping for results like:

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

but this don't get any of the null rows from either side?

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)

---

This works - combining the "key" for the join in two separate select statements?

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

---

But this doesn't - joining on the combined values - only when they are separate tables?

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)

解决方案

The where is killing the outer join.
A column cannot be both null and = to a value.
Put the conditions in the join.

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

这篇关于完全外部联接不返回所有行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！