title: SQL Server Rewriting Left Join
body: <p>I was having a problem with a larger query in SQL Server which I traced back to this section of code which isn't performing as expected.</p>
  
  <pre><code>SELECT item_name,item_num,rst_units
  FROM tbl_item left join tbl_sales_regional on item_num=rst_item
  WHERE rst_month=7 and rst_customer='AB123' 
  </code></pre>
  
  <p>The first table (tbl_item) has 10,000 records. The second table (tbl_sales_regional) has 83 for the shown criteria.</p>
  
  <p>Instead of returning 10,000 records with 9,917 nulls, the execution plan shows SQL Server has rewritten as an inner join and consequently returns 83 results.</p>
  
  <p>In order to achieve the intended result, I have to join off a subquery. Can someone provide an explanation why this doesn't work?</p>