title: Use of scalar functions in Join condition
body: <p>I am joining with a table to retrieve a value but in order to join I need to do some logic because both the tables do not have a common value in place. So the logic is to do a top 1 and get a certain value from a common table for both the inputs. I am planning to put the logic inside a scalar function and then call it in the join condition.</p>
  
  <p>I have a simple join as something like this</p>
  
  <pre><code>LEFT JOIN &lt;IndexTable&gt; Tbl1 ON Tbl1.PrimaryKey = MainTbl.PrimaryKey
  </code></pre>
  
  <p>I intend to change this into something like this using a user defined scalar function</p>
  
  <pre><code>LEFT JOIN &lt;IndexTable&gt; Tbl1 
  ON dbo.fnGetCommonID(Tbl1.PrimaryKey) = dbo.fnGetCommonID(MainTbl.PrimaryKey)
  </code></pre>
  
  <p>In terms of performance is it good to use it this way or is there any better way for this.</p>