title: Include records that has a null value on INNER JOIN SQL Server query over a tree table
body: <p>I'm querying over a table that has nested references to itself, and I need to get records has a null reference on the INNER JOIN value which i'm using. In other words, the tree table records may not reach the same depth and I need to get all of them. </p>
  
  <p>The query I'm using atm is:</p>
  
  <pre><code>SELECT DISTINCT &lt;valuesThatINeed&gt;
  FROM atable foo
     INNER JOIN treetable bar ON foo.id_bar = bar.id
     INNER JOIN treetable bar2 ON bar.id_bar = bar2.id
     INNER JOIN treetable bar3 ON bar2.id_bar = bar3.id
  WHERE
     &lt;constraints&gt;
  </code></pre>
  
  <p>I've read that I can extend the INNER JOIN condition by adding an OR statement asking if bar.id_bar IS NULL, but I cannot reach further levels if I do that: </p>
  
  <pre><code>SELECT DISTINCT &lt;valuesThatINeed&gt;
  FROM atable foo
     INNER JOIN treetable bar ON foo.id_bar = bar.id 
     INNER JOIN treetable bar2 ON bar.id_bar = bar2.id OR (bar.id_bar IS NULL) 
     INNER JOIN treetable bar3 ON bar2.id_bar = bar3.id
  WHERE
     &lt;constraints&gt;
  </code></pre>