title: Invalid results from SQL Server "NOT IN" clause
body: <p>I have run a query on our SQL Server 2012 which returned no results. I discovered that this was incorrect and I SHOULD have gotten 16 records. I changed the query and get the answer expected but I am at a loss to understand why my original query did not work as expected.  </p>
  
  <p>So my ORIGINAL query which returned no results was:</p>
  
  <pre><code>SELECT
      WPB.[ID number]
  FROM
      [Fact].[REPORT].[WPB_LIST_OF_IDS] WPB
  WHERE
      [ID number] NOT IN (SELECT DISTINCT IdNumber 
                          FROM MasterData.Dimension.Customer DC)
  </code></pre>
  
  <p>The reworked query is this:</p>
  
  <pre><code>SELECT
      WPB.[ID number]
  FROM
      [Fact].[REPORT].[WPB_LIST_OF_IDS] WPB
  LEFT JOIN
      MasterData.Dimension.Customer DC ON WPB.[ID number] = DC.IdNumber
  WHERE
      DC.IdNumber IS NULL
  </code></pre>
  
  <p>Can anyone tell me WHY the first query (which incidentally runs in fractions of a second vs the 2nd which takes a minute) does not work? I don't want to repeat this mistake in the future!</p>