How to use select statement in join condition in Impala sql

user_11834668 0
Viewed 136

I am learning Impala sql and need to convert a sql query into impala equivalent which is something like this:

select distinct t1.c1, t1.c2 
  from table1 t1
       join table2 t2 
       on t2.c1=t1.c1 and (t2.c2 is null 
                           or t2.c2 in (select c1 from table3 
                                                 where 'some conditions')
                          )

When I am executing this query in impala I am getting error as "Could not resolve table reference table3". Although this table3 is present in the database which I am using.

Can anyone please guide what is happening and why am I getting this table not found error.

Also please suggest how to implement this sql code into Impala equivalent.

impala join oracle sql
1 Answers

It does not support queries in ON join condition, rewrite query like this:

select distinct t1.c1, t1.c2 
  from table1 t1
       join table2 t2 on t2.c1=t1.c1
       left join (--prevent unintended duplication by join if c1 is not unique
                  select distinct c1 from 
                  table3 where 'some conditions') t3 on t2.c2=c3.c1  
 where t2.c2 is null --not joined with c3 because of null
    or t3.c1 is not null --or joined with c3
user_27003440
Related