title: SQL Server Left Join With 'Or' Operator
body: <p>I have a four tables, TopLevelParent, two mid level tables MidParentA and MidParentB, and a Child table which can have a parent of MidParentA or MidParentB (One or the other midParent must be in place). Both mid level tables have a parent table of TopLevelParent.</p>
  
  <p>The Top Level table look like this:</p>
  
  <pre><code>TopLevelId | Name
  --------------------------
  1          | name1   
  2          | name2   
  </code></pre>
  
  <p>The MidParent tables look like this:</p>
  
  <pre><code>MidParentAId | TopLevelParentId |           MidParentBId | TopLevelParentId |
  ------------------------------------       ------------------------------------
  1            |        1         |           1            |        1         |
  2            |        1         |           2            |        1         |
  </code></pre>
  
  <p>The Child table look like this:</p>
  
  <pre><code>ChildId | MidParentAId | MidParentBId
  --------------------------------
  1       |     1        |   NULL
  2       |    NULL      |     2
  </code></pre>
  
  <p>I have used the following left join in a larger stored procedure which is timing out, and it looks like the OR operator on the last left join is the culprit:</p>
  
  <pre><code>SELECT *    
  FROM TopLevelParent tlp
  LEFT JOIN MidParentA a ON tlp.TopLevelPatientId = a.TopLevelPatientId
  LEFT JOIN MidParentB a ON tlp.TopLevelPatientId = b.TopLevelPatientId
  LEFT JOIN Child c ON c.ParentAId = a.ParentAId OR c.ParentBId = b.ParentBId
  </code></pre>
  
  <p>Is there a more performant way to do this join?</p>