我想你想要：

select t2.*, coalesce(t1.location, t1n.location) as location
from table2 t2 left join
     table1 t1
     on t2.product2 = t1.product1 left join
     table1 t1n
     on t2.product2 = t1n.new_prod_name and t1.product1 is null;

不能有条件地联接，但可以联接两者，然后有条件地选择返回值。我没有这方面的任何样本数据，但我会尝试一下。
请参阅此处的完整示例。

Select t1_id
     , coalesce(t2.product2,t2n.product2,t1.product1) product
     , t1.location
     , coalesce(t2.quantity,t2n.quantity) product
     , coalesce(t2.adate,t2n.adate) "date"  
  from table1       t1 
  Left join table2  t2 
         on (t2.product2 = t1.product1)  
  left join  table2 t2n
         on (t2n.product2 = t1.new_product_name)
order by t1_id;