hive get list of non existing and existing data

问题:

Two tables :

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

Desired output:

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

Query tried:

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

Output:

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

Thanks in advance.

回答1:

Remove the where clause.Just the left outer join will get you the results

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