hive获取不存在和现有数据的列表

4c8rllxm  于 2021-06-26  发布在  Hive
关注(0)|答案(1)|浏览(435)

两张table:

Reg                      Global
ID | uom                 ID  | uom    
------------------           ----------------
1  | kg                   1  | kg
1  | gm                   1  | gm
1  | ml                   3  | pl
3  | pl

期望输出:

ID | reg    | glob   
------------------  
1  | kg      | kg
1  | gm      | gm
1  | ml      | null
3  | pl      | pl

已尝试查询:

SELECT reg.id,  reg.UOM  ,glob.uom
FROM reg
LEFT JOIN global glob
ON reg.id=reg.id  and reg.uom = glob.uom
WHERE  glob.uom is null and reg.id =1

输出:

reg.id | reg.uom | glob.uom 
1      | ml      | null

提前谢谢。

ghg1uchk

ghg1uchk1#

删除where子句。只需左外连接即可得到结果

select Reg.ID, Reg.uom as reg, Global.uom as glob
from Reg
left outer join Global on Reg.ID = Global.ID and Reg.uom = Global.uom

相关问题