title: Case statement in LEFT OUTER JOIN slowing the query in SQL Server 2008
body: <p>I am getting a problem with my LEFT OUTER JOIN. I have a set of queries which gives me about 80,000 to 1,00000 records in a #Temp Table. Now when I LEFT OUTER JOIN this #Temp table with another table I have to put a CASE statement i.e. if the records are not found when  joining with a particular column then take that particular column value and find its subsequent value in another table which has the matching records. The query is working fine for a particular data but for larger data it just goes on executing or just takes too much time. My query is like:</p>
  
  <pre><code>SELECT * FROM #Temp 
  LEFT OUTER JOIN TABLE1 ON #Temp.Materialcode = 
       CASE WHEN TABLE1.MaterialCode LIKE 'HY%' 
                 THEN TABLE1.MaterialCode 
                 ELSE REPLACE(TABLE1.MaterialCode,
                              TABLE1.MaterialCode,
                              (SELECT NewMaterialCode 
                                      FROM TABLE2 
                                      WHERE OldMaterialCode = TABLE1.MaterialCode))
       END 
  </code></pre>
  
  <p>Here TABLE2 has got only two columns NewMaterialCode and OldMetarialCode. What I have to do is if the Material Code is not found in TABLE1  LIKE 'HY%' type then it should take that material code and look for its subsequent NewMaterialCode in TABLE2 to get both types of records having 'HY' type and non 'HY' type.  I think I made my problem clear. Any help would be greatly appreciated. </p>