title: Why cross apply is faster than inner join?
body: <p>I have the following functions:</p>
  
  <pre><code>FunctionA - returns Object ID and Detail ID
  FunctionB - returns Detail ID and Detail Name
  </code></pre>
  
  <p>The following query is used to extract <code>Object ID</code>, <code>Detail ID</code> and <code>Detail Name</code>:</p>
  
  <pre><code>SELECT FunctionA.ID
        ,FunctionA.DetailID
        ,FunctionB.DetailName
  FROM FunctionA (...)
  INNER JOIN FunctionB (...) 
      ON FunctionA.DetailID = FunctionB.DetailID
  </code></pre>
  
  <p>The screenshot below display its execution plan cost (it takes 32 seconds):</p>
  
  <p><img src="https://i.stack.imgur.com/4cOWO.png" alt="enter image description here"></p>
  
  <p>In the following query I have changed the query to use <code>cross apply</code>  instead <code>inner join</code> and made the <code>FunctionB</code> to return <code>Detail Name</code> for specific <code>Detail ID</code>:</p>
  
  <pre><code>SELECT FunctionA.ID
        ,FunctionA.DetailID
        ,FunctionB.DetailName
  FROM FunctionA (...)
  CROSS APPLY FunctionB (FunctionA.DetailID) 
      ON FunctionA.DetailID = FunctionB.DetailID
  </code></pre>
  
  <p>The screenshot below display its execution plan cost (it takes 3 seconds):</p>
  
  <p><img src="https://i.stack.imgur.com/g57df.png" alt="enter image description here"></p>
  
  <p>In the first case <code>FunctionB</code> returns all pairs <code>Detail ID</code> and <code>Detail Name</code> and normally it takes a lot of time.
  In the second case, <code>FunctionB</code> is executed faster because it returns only <code>Detail Name</code> for specific <code>Detail ID</code>, but it is executed for each <code>Object ID</code>.</p>
  
  <p>Why the first case is so slower? Is SQL Server executing the <code>FunctionB</code> in the second case for each row, or it is caching results and avoids execution of the function
  with same parameter?</p>