有时候查询hive表的时候需要用到两次的left outer join ,在这里做如下笔记
select x.id,x.name from (
select m.id ,m.name from (
select id , name from A
) m left outer join (
select id , name from B
) n on m.id = n.id where n.id is null group by m.id ,m.name
) x left outer join (
select id ,name from C
) y on x.id = y.id where y.id is null group by x.id,x.name
主要想表达的意思是:结果集m和结果集n左外连接取关联结果集n的id为空的对应行m.id,m.name的记录,这个结果集作为x 和结果集y再做左外关联 ,取结果集y的id 为空的对应行x.id,x.name的记录.