title: Ignoring null values in join SQL server
body: <p>I need to fetch details from Table 2, based on the inputs in Table 1.
  There can be null values in Table 1, but the null inputs needs to be ignored in the comparison when doing join to Table2</p>
  
  <p>For example - The output should include all papers belonging to the subject if paper is null in Table1.
  Can you suggest a way I could ignore nulls in joins? Query needs to be in SQL Server</p>
  
  <pre><code>ID  Paper   Subject Department
  ------------------------------
  1   paper1  Math    Dept1
  2   null    Math    Dept1
  3   null    null    Dept2
  4   paper4  Science Dept2
  </code></pre>
  
  <p>Table2</p>
  
  <pre><code>Paper   Subject Department  details1    details2    details3
  paper1  Math    Dept1        D1          D2          D3
  paper2  Math    Dept1        D1          D2          D3
  Paper1  Science Dept2        D1          D2          D3
  Paper2  Science Dept2        D1          D2          D3
  Paper3  Science Dept1        D1          D2          D3
  Paper4  Science Dept2        D1          D2          D3
  </code></pre>
  
  <p>output</p>
  
  <pre><code> Paper  Subject Department  Details1    Details2    Details3
   paper1 Math    Dept1       D1          D2          D3
   paper2 Math    Dept1       D1          D2          D3
   Paper1 Science Dept2       D1          D2          D3
   Paper2 Science Dept2       D1          D2          D3
   paper4 Science Dept2       D1          D2          D3
  </code></pre>