title: What are the dangers of using subqueries with IN clauses?
body: <p>I recently uncovered a query (which has since been lost) that seemed to have an issue stemming from the use of a subquery alongside an IN clause. For example:</p>
  
  <pre><code>SELECT * FROM Table1 WHERE Column1 IN (SELECT Column1 FROM Table2)
  </code></pre>
  
  <p>The issue I seemed to notice was that when I used IN and NOT IN, the figures with each did not account for the total records in Table1. Whilst Table1 did not have any NULL values in Column1, I believe that Table2 did. Could this have caused an issue? I appreciate that comparing something to NULL via the standard logical operators will not work in T-SQL, but I have not been able to find anything regarding IN clauses with subqueries.</p>
  
  <p>I unfortunately do not have the original query, and when I tried to reproduce the issue using some very general code, I could not. So I appreciate this is perhaps a vague question, but it would be useful to know of any 'dangers' that people may not be generally aware of when using the IN/NOT IN operators with a subquery, specifically relating to NULLS. Any other general dangers not necessarily relating to NULLS would also be useful. </p>
  
  <p>I would assume the general advice would be to use a JOIN, however this is primarily so that I can identify possible issues in existing code and remediate if necessary.</p>