title: Performance issue on conditional join in select query
body: <p>Right now, I am selecting data with this query (simplyfied):</p>
  
  <pre><code>SELECT person.*, pty.*
  FROM PERSON person
       LEFT JOIN PARTNER partner 
       ON partner.FK_BPE_ISTC_ID = person.BSPE_ISTC_ID
       INNER JOIN PTY pty 
       ON pty.PTY_ISTC_ID = IIF(person.FORGN_ISTC_IND != 0, person.FORGN_ISTC_IND, partner.FRGN_ISTC_ID)
  </code></pre>
  
  <p>The Problem is that it takes so much time to complete and the execution plan looks like this:</p>
  
  <p><a href="https://i.stack.imgur.com/R8xj5.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/R8xj5.png" alt="Execution plan"></a></p>
  
  <p>Almost 20 million rows (picture) need to be processed to get me 25 thousand rows in the end.</p>
  
  <p>Is there a better approach to join the PTY table with the condition?</p>