title: SQL Server JOIN missing NULL values from left table
body: <p>I have two tables in SQL Server:</p>
  <pre><code>  Table1:                               Table2:
  
    Col1(PK):      Col2:                  Col1:              
    ------------------------              ------
    1              1                      1                     
    2              &lt;null&gt;                 2                 
    3              5                      10                     
    4              &lt;null&gt;                 11                 
    5              12                     12                 
  </code></pre>
  <p>Now I want join these tables on <code>Col2</code> but in result I want have all rows with <code>Col2</code> where value is null and if not null only rows that <code>Col2</code> exist in <code>Table2</code>.</p>
  <p>When I try use <code>Left Join</code> in result have row from <code>Col1(PK) = 3</code> but <code>Col2 = 5</code> does not exist in <code>Table2</code>.</p>
  <p>How can I achieve it?</p>
  <p>I tried something like this, but it only returns the matched lines and ignores the null ones</p>
  <pre class="lang-sql prettyprint-override"><code>select * 
  from Table1 t1 
  join Table2 t2 on t1.Col2 = t2.Col1 or t1.Col2 is null and t2.Col1 is null
  </code></pre>
  <p>Expected rows in result Col1(PK) 1,2,4,5.
  Because null in T1.Col2 is ok, and in T1.Col2 value in row 1(1) and 5(12) exist in T2.Col1</p>
  <p>Something like
  If T1.Col2 == null =&gt; Is Ok Can Return
  If T1.Col2 != null =&gt; Check If Exists Value in T2.Col1 =&gt; Exists Is ok Can Return, Not Exists Skip</p>