title: SQL WHERE on a large table -> Join to small table first or put FK directly in the WHERE clause?
body: <p>What is better in a <code>WHERE</code> clause?</p>
  
  <p>I have a large table, with a FK to a small table. I can search on the FK directly, or I can join to the FK table and set the <code>WHERE</code> restriction on the joined table. What is better / preferable?</p>
  
  <p>So this:</p>
  
  <pre><code>SELECT lt.* FROM LargeTable lt 
  WHERE lt.SomeId in(12,55)
  </code></pre>
  
  <p>Or this:</p>
  
  <pre><code>SELECT lt.* FROM LargeTable lt 
  INNER JOIN SmallTable st ON lt.SomeId=st.ItemId
  WHERE st.Id in(12,55)
  </code></pre>
  
  <p><br />
  I tested this with <code>Set statistics time on</code>, but I didn't expect this as a result. Who can explain what happens here?</p>
  
  <p>First test without join: </p>
  
  <pre><code>(946 row(s) affected)
   SQL Server Execution Times:
     CPU time = 1544 ms,  elapsed time = 1580 ms.
  </code></pre>
  
  <p>Second test with the join</p>
  
  <pre><code>(946 row(s) affected)
   SQL Server Execution Times:
     CPU time = 2636 ms,  elapsed time = 366 ms.
  </code></pre>
  
  <p>EDIT: When i do <code>SELECT Id</code> instead of <code>SELECT *</code>, then the first query without join has a lower elapsed time, and the query cost in the execution plan is 25% for no join vs 75% for the query with the join.</p>