title: LEFT JOIN Significantly faster than INNER JOIN
body: <p>I have a table (<code>MainTable</code>) with a bit over 600,000 records. It joins onto itself via a 2nd table (<code>JoinTable</code>) in a parent/child type relationship:</p>
  
  <pre><code>SELECT   Child.ID, Parent.ID
  FROM     MainTable
  AS       Child
  JOIN     JoinTable
        ON Child.ID = JoinTable.ID
  JOIN     MainTable
  AS       Parent
        ON Parent.ID = JoinTable.ParentID
       AND Parent.SomeOtherData = Child.SomeOtherData
  </code></pre>
  
  <p>I know that every child record has a parent record and the data in JoinTable is acurate.</p>
  
  <p>When I run this query it takes literally minutes to run. However if I join to Parent using a Left Join then it takes &lt; 1 second to run:</p>
  
  <pre><code>SELECT   Child.ID, Parent.ID
  FROM     MainTable
  AS       Child
  JOIN     JoinTable
        ON Child.ID = JoinTable.ID
  LEFT JOIN MainTable
  AS       Parent
        ON Parent.ID = JoinTable.ParentID
       AND Parent.SomeOtherData = Child.SomeOtherData
  WHERE    ...[some info to make sure we don't select parent records in the child dataset]...
  </code></pre>
  
  <p>I understand the difference in the results between an <code>INNER JOIN</code> and a <code>LEFT JOIN</code>. In this case it is returning exactly the same result as every child has a parent. If I let both queries run, I can compare the datasets and they are exactly the same.</p>
  
  <p>Why is it that a <code>LEFT JOIN</code> runs so much faster than an <code>INNER JOIN</code>?</p>
  
  <hr>
  
  <p>UPDATE
  Checked the query plans and when using an inner join it starts with the Parent dataset. When doing a left join it starts with the child dataset.</p>
  
  <p>The indexes it uses are all the same.</p>
  
  <p>Can I force it to always start with the child? Using a left join works, it just feels wrong.</p>
  
  <hr>
  
  <p>Similar questions have been asked here before, but none seem to answer my question.</p>
  
  <p>e.g. the selected answer in <a href="https://stackoverflow.com/questions/2726657/inner-join-vs-left-join-performance-in-sql-server">INNER JOIN vs LEFT JOIN performance in SQL Server</a> says that Left Joins are always slower than Inner joins. The argument makes sense, but it's not what I'm seeing.</p>