Oracle INNER JOIN ON CASE WHEN condition

Question

I have two subqueries I need to (inner) join on a column ("id"). Unfortunately, some of the values I am joining on have been altered somewhere before, so the ON condition of the INNER JOIN is supposed to be conditionally selected out of two, depending on the value of another column ("discriminator"). A simplified example follows:

Example result set query 1 (q1)

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

Example result set query 2 (q2)

ID       OTHERDATA     
====================
1 1      foo          
A B      bar               
EE F     foobar
G HH     barfoo
11       foofoo

If the discriminator is "yes" 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.

Expected Example Result

ID       DATA                OTHERDATA
==========================================
1 1      Whatever            foo
11       And this            foofoo
A B      NULL                bar
EE F     But this one        foobar

Can I somehow do a CASE WHEN in the ON condition? I tried the following

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
;

but I get a missing keyword error. I fiddled around but remain unable to correctly use CASE WHEN in the ON condition.

I know I could use a UNION to do something along the lines of

SELECT q2.id
    , q1.data
    , q2.otherdata
FROM ((...somequery...) q1 INNER JOIN (...somequery...) q2 
           ON q1.id = q2.id
               AND (discriminator <> '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
;

but this requires the subqueries being executed twice, doesn't it? I'd rather find a way that avoids that.

Is it possible to use a CASE WHEN statement in the INNER JOIN clause in a way that conditionally uses one out of two ON conditions? Or is there another elegant way?

Answer 1

Yes, it is possible:

SELECT q2.id
    , q1.data
    , q2.otherdata
FROM ((...somequery...) q1
      INNER JOIN
      (...somequery...) q2
      ON (CASE WHEN discriminator = 'yes' AND TRIM(q1.id) = CONCAT('2', REPLACE(RTRIM(q2.id), ' ', '')) THEN 1
             WHEN (discriminator <> 'yes' OR discriminator IS NULL) AND q1.id = q2.id THEN 1
      END) = 1
     )
ORDER BY id
;