Using if statement to select table inside FROM statement in ORACLE

Question

please someone help me. I want to convert this query from mybatis to ORACLE

SELECT *
FROM TABLE_A
   <if paramA is not null>
     , ( SELECT *
         FROM TABLE_B
       ) B
   </if>
WHERE ....
   <if paramA is not null>
      A.key = B.key
   </if>

i'm currently stuck at that IF tag inside FROM.

Answer 1

Oracle does not allow you to conditionally include tables.

One option may be to always join TABLE_B and filter on the parameter (as a bind parameter) in the join condition:

SELECT *
FROM   TABLE_A A
       LEFT OUTER JOIN TABLE_B
       ON A.key = B.key AND :paramA IS NOT NULL

It is not quite the same as, when the :paramA bind variable is NULL then the TABLE_B columns will be included in the output but the values of those TABLE_B columns will all be NULL.