title: TSQL: Using replace function in select with join
body: <p><strong>Background.</strong> I'm using SQL Server. I have two tables in database:</p>
  
  <pre><code>Vendors(Id, Name, Description)
  Products(Id, VendorId, Name, Description)
  </code></pre>
  
  <p>Values in <code>Id</code> column are formatted with prefix <code>'ID_'</code> in <code>Vendor</code> table.</p>
  
  <p>Values in <code>VendorId</code> column are formatted with prefix <code>'VE_'</code> in <code>Products</code> table.</p>
  
  <p><strong>E.g.</strong> <code>'VE_001245'</code> in <code>Products</code> refers to <code>'ID_001245'</code> in <code>Vendors</code>.</p>
  
  <p>(Please, do not propose to change this concept, do not care about database scheme, do not suggest adding foreign key. All it is just for illustration.)</p>
  
  <p><strong>Question</strong>: which one of following queries is best in performance context and why?</p>
  
  <ol>
  <li><p>Use <code>replace</code> function in inner <code>select</code>:</p>
  
  <pre><code>select v.* from Vendors v
  inner join
  (
      select distinct replace(VendorId, 'VE_', 'ID_') as Id
      from Products
  ) list
  on v.Id = list.Id
  </code></pre></li>
  <li><p>Use <code>replace</code> function in <code>on</code>-statement:</p>
  
  <pre><code>select v.* from Vendors v
  inner join
  (
      select distinct VendorId as Id
      from Products
  ) list
  on v.Id = replace(list.Id, 'VE_', 'ID_')
  </code></pre></li>
  </ol>
  
  <p><strong>Edit.</strong> There is only clustered index in each table (by <code>Id</code> column). Each table can contains millions rows.</p>