Performance on SQL Join
Your statistics need updating. 

See Statistics, row estimations and the ascending date column. 

Recent dates are not proportionately represented in the statistics and so SQL Server underestimates the number of rows that will match the &gt;= 2012-12-31 predicate (as shown by the Estimated no of rows of 1 vs actual 13922). So it chooses a plan with nested loops and a seek on the other table. 

The use of right(tbl1.id,7) = right(tbl2.id,7) prevents a seek being used so you get the hash join more suitable for the actual number of rows involved.

If you update  the statistics on that column it should choose a more appropriate plan naturally. You might also consider using Trace Flags 2389 &amp; 2390 to make this issue less likely in the future.