hive之full outer join（全连接）使用

前言

• full outer join结合了 LEFT JOIN 和 RIGHT JOIN 的结果，并使用NULL值作为两侧缺失匹配结果。

---

语法 :

SELECT 
    table1.column_name(s),table2.column_name(s) 
FROM table1 
    FULL OUTER JOIN table2 
ON table1.column_name = table2.column_name;
1
2
3
4
5

• 相当于:left join + union + right join

SELECT 
    table1.column_name(s),table2.column_name(s)
FROM table1 
    LEFT OUTER JOIN table2 
ON table1.column_name = table2.column_name
UNION
SELECT 
    table1.column_name(s),table2.column_name(s)
FROM table1 
    RIGHT OUTER JOIN table2 
ON table1.column_name = table2.column_name;
1
2
3
4
5
6
7
8
9
10
11

• 样例 :

select 
CASE WHEN cast(yz.province_code as string) IS NOT NULL THEN yz.province_code ELSE hy.access_code END AS pro_code,
CASE WHEN yz.vehicle_no IS NOT NULL THEN yz.vehicle_no  ELSE hy.vehicle_no END AS veh_no,
CASE WHEN cast(yz.plate_color_code as string) IS NOT NULL THEN yz.plate_color_code ELSE hy.vehicle_color END AS plate_color,
CASE WHEN
MD5(CONCAT(yz.vehicle_no, yz.plate_color_code, yz.province_code, yz.owner_name
          )) IS NULL 
THEN  MD5(CONCAT(hy.vehicle_no, hy.vehicle_color, hy.access_code, hy.owner_name
          ))
WHEN  MD5(CONCAT(hy.vehicle_no, hy.vehicle_color, hy.access_code, hy.owner_name
                      
          )) IS NULL 
THEN MD5(CONCAT(yz.vehicle_no, yz.plate_color_code, yz.province_code, yz.owner_name
          )) 
          ELSE MD5(CONCAT(yz.vehicle_no, yz.plate_color_code, yz.province_code, yz.owner_name
          )) END AS crc_md5,
		  ${month1} as diff_month,
CASE
        WHEN yz.vehicle_no IS NULL AND yz.plate_color_code IS NULL THEN 1 -- A数据集存在，B数据集不存在
        WHEN hy.vehicle_no IS NULL AND hy.vehicle_color IS NULL THEN 3 -- A数据集不存在，B数据集存在
        WHEN MD5(CONCAT(hy.vehicle_no, hy.vehicle_color, hy.access_code, hy.owner_name)) != MD5(CONCAT(yz.vehicle_no, yz.plate_color_code, yz.province_code, hy.owner_name)) THEN 2 -- 鉴权值不一样的车辆数据
        ELSE 4 -- 相等的情况下
    END AS flag
from yz full outer join
hy ON 
yz.vehicle_no = hy.vehicle_no and yz.plate_color_code = hy.vehicle_color
HAVING  flag != 4 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

---

总结

如果此篇文章有帮助到您, 希望打大佬们能关注、点赞、收藏、评论支持一波，非常感谢大家！
如果有不对的地方请指正!!!

参考1