insert into where not exists in hive

问题:

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.

回答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