title: Oracle INNER JOIN ON CASE WHEN condition
body: <p>I have two subqueries I need to (inner) join on a column (&quot;id&quot;). Unfortunately, some of the values I am joining on have been altered somewhere before, so the <code>ON</code> condition of the <code>INNER JOIN</code> is supposed to be conditionally selected out of two, depending on the value of another column (&quot;discriminator&quot;). A simplified example follows:</p>
  <p>Example result set query 1 (q1)</p>
  <pre><code>ID       DATA                DISCRIMINATOR
  ==========================================
  1 1      Whatever            NULL
  2AB      NULL                yes
  4f-G     Could be anything   no
  2CD      This one not        yes
  2EEF     But this one        yes
  11       And this            no
  211      Last one            no
  </code></pre>
  <p>Example result set query 2 (q2)</p>
  <pre><code>ID       OTHERDATA     
  ====================
  1 1      foo          
  A B      bar               
  EE F     foobar
  G HH     barfoo
  11       foofoo
  </code></pre>
  <p>If the discriminator is &quot;yes&quot; the IDs in q1 were altered by prepending '2' and removing all the whitespace, otherwise, they are the same. I still want to be able to correctly join the tables, and keep the ID from q2 in the join.</p>
  <p>Expected Example Result</p>
  <pre><code>ID       DATA                OTHERDATA
  ==========================================
  1 1      Whatever            foo
  11       And this            foofoo
  A B      NULL                bar
  EE F     But this one        foobar
  </code></pre>
  <p>Can I somehow do a <code>CASE WHEN</code> in the <code>ON</code> condition? I tried the following</p>
  <pre><code>SELECT q2.id
      , q1.data
      , q2.otherdata
  FROM ((...somequery...) q1
        INNER JOIN
        (...somequery...) q2
        ON CASE WHEN discriminator = 'yes' THEN TRIM(q1.id) = CONCAT('2', REPLACE(RTRIM(q2.id), ' ', ''))
            ELSE q1.id = q2.id
        END
       )
  ORDER BY id
  ;
  </code></pre>
  <p>but I get a <code>missing keyword</code> error. I fiddled around but remain unable to correctly use <code>CASE WHEN</code> in the <code>ON</code> condition.</p>
  <p>I know I could use a <code>UNION</code> to do something along the lines of</p>
  <pre><code>SELECT q2.id
      , q1.data
      , q2.otherdata
  FROM ((...somequery...) q1 INNER JOIN (...somequery...) q2 
             ON q1.id = q2.id
                 AND (discriminator &lt;&gt; 'yes' OR discriminator IS NULL)
           )
  UNION
  SELECT q2.id
      , q1.data
      , q2.otherdata
  FROM ((...somequery...) q1 INNER JOIN (...somequery...) q2 
             ON TRIM(q1.id) = CONCAT('2', REPLACE(RTRIM(q2.id), ' ', ''))
                 AND discriminator = 'yes'
            )
  ORDER BY id
  ;
  </code></pre>
  <p>but this requires the subqueries being executed twice, doesn't it? I'd rather find a way that avoids that.</p>
  <p>Is it possible to use a <code>CASE WHEN</code> statement in the <code>INNER JOIN</code> clause in a way that conditionally uses one out of two <code>ON</code> conditions? Or is there another elegant way?</p>