前言
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;
- 相当于: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;
- 样例 :
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
总结
如果此篇文章有帮助到您, 希望打大佬们能
关注
、点赞
、收藏
、评论
支持一波,非常感谢大家!
如果有不对的地方请指正!!!