insert into where not exists in hive

Question

I need the hive syntax for this equivalent in ansi sql

insert into tablea
(id)
select id 
from tableb
where id not in (select id from tablea)

so tablea contains no duplicates and only new ids from tableb are inserted.

Answer 1

Use left outer join with a filter that the tablea.id is null:

insert overwrite into tablea (id)
select b.id from tableb b left outer join table tablea a
 on a.id = b.id
where a.id is null