title: Full outer join not returning all rows?
body: <p>I have a table that contains multiple records for multiple dates.</p>
  
  <p>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.</p>
  
  <p>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 <code>INNER JOIN</code> (I tested this).</p>
  
  <p>I am hoping for results like:</p>
  
  <pre><code>001     000123    009    NULL    1000
  001     000124    009    1000    1000
  001     000125    009    1000    1000
  001     000126    009    1000    NULL
  </code></pre>
  
  <p>but this don't get any of the null rows from either side?</p>
  
  <pre><code>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)
  </code></pre>
  
  <hr>
  
  <p>This works - combining the "key" for the join in two separate select statements?</p>
  
  <pre><code>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 &lt;&gt; C.PrincipleAmount
  </code></pre>
  
  <hr>
  
  <p>But this doesn't - joining on the combined values - only when they are separate tables?</p>
  
  <pre><code>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)
  </code></pre>