title: Improve/optimize LEFT JOIN in SQL Server
body: <p>I have a decently long (10 tables, about 60k records each) query, all of those tables are joined using a left join since they could contain null values (all of them).  </p>
  
  <p>I'm seeing a huge performance hit and I tracked it down to this bit of code.</p>
  
  <pre><code>SELECT * 
  FROM MAIN_TABLE d 
  LEFT JOIN INSURANCES i on i.IMREDEM_CODE = d.IMREDEM_CODE 
                        and i.INS_TERMINATIONDATE IS NULL 
                        and INS_RANK = 0
                        and i.IMREINS_CODE = (SELECT TOP 1 IMREINS_CODE 
                                                from INSURANCES i2
                                               WHERE i2.IMREDEM_CODE = i.IMREDEM_CODE 
                                                 and i2.INS_TERMINATIONDATE IS NULL 
                                                 and i2.INS_RANK = 0
                                            ORDER BY TAG_SYSTEMDATE DESC)
  </code></pre>
  
  <p>Basically what I am needing to do is that the insurance table could contain 0 or many records because when they update the insurance it performs an insert not an update for audit purposes.  So I have to join the table twice, on a left join.  Further I need to do this query twice for primary and secondary insurance (primary is rank = 0 and secondary is rank =1.  The IMREDEM_CODE is a PK for the D table and a FK for the i table.  </p>
  
  <p><strong>The answer is here</strong>:</p>
  
  <pre><code>left join INSURANCES i on i.IMREDEM_CODE = d.IMREDEM_CODE 
  and i.IMREINS_CODE = (SELECT max(imreins_code) FROM INSURANCES i2  WHERE i2.IMREDEM_CODE = i.IMREDEM_CODE and i2.INS_TERMINATIONDATE IS NULL and i2.INS_RANK = 0)
  </code></pre>