关于hive上NOT IN改为左连接left join

Question

DROP TABLE IF EXISTS TMP_YWJH_YKDZ_04;
CREATE TABLE TMP_YWJH_YKDZ_04 AS
SELECT A.*, B.LNX_NUM LNX_NUM
FROM SRBZ_HIVE.TMP_YWJH_YKDZ_02 A, SRBZ_HIVE.TMP_YWJH_YKDZ_03 B
WHERE A.CERTI_NUMBER = B.CERTI_NUMBER
AND A.CERTI_NUMBER NOT IN (SELECT CERTI_NUMBER FROM SRBZ_HIVE.TMP_YWJH_YKDZ_02 WHERE create_date>to_date(from_u)

求帮忙看看 如何改成left join

Answer 1

DROP TABLE IF EXISTS TMP_YWJH_YKDZ_04;
CREATE TABLE TMP_YWJH_YKDZ_04 AS
SELECT A.*, B.LNX_NUM LNX_NUM
FROM SRBZ_HIVE.TMP_YWJH_YKDZ_02 A
join SRBZ_HIVE.TMP_YWJH_YKDZ_03 B
on A.CERTI_NUMBER = B.CERTI_NUMBER
left join (SELECT CERTI_NUMBER FROM SRBZ_HIVE.TMP_YWJH_YKDZ_02 WHERE create_date>to_date(from_u)) C
on A.CERTI_NUMBER=C.CERTI_NUMBER
WHERE C.CERTI_NUMBER is null