hive：语法：怎样写exist/in以及not exists/not in子句 和 字段嵌套子查询

1.hive中没有exist或not exist 语句，但是关系型数据库中是有这种语法的，比如mysql和sqlserver

但是通过语法的转换，hive可以使用另外的语法得到相应的结果。

如mysql语句：

SELECT
    CASE
        WHEN d.Name IS NOT NULL
        THEN d.Name
        ELSE ''
    END regionName,
    CASE
        WHEN c.Name IS NOT NULL
        THEN c.Name
        ELSE ''
    END branchName,
    CASE
        WHEN A.WorkNo IS NOT NULL
        THEN A.WorkNo
        ELSE ''
    END workNo,
    CASE
        WHEN A.Fullname IS NOT NULL
        THEN A.Fullname
        ELSE ''
    END employeeName,
    CASE
        WHEN A.EntryDate IS NOT NULL
        THEN A.EntryDate
        ELSE ''
    END entryDate
FROM
    t_employees A
LEFT JOIN
    t_branches c
ON
    A.BranchNo=c.Number
LEFT JOIN
    t_regions d
ON
    concat(concat('%{',A.BranchNo),'}%')
WHERE
    RankSery LIKE 'C%'
AND IsLeave IS NULL
AND branchno NOT LIKE '%lni'
AND branchno NOT LIKE '%yunying'
AND NOT EXISTS
    (
        SELECT
            1
        FROM
            t_recomrecords B
        WHERE
            A.Number=B.EmployeeNo
        AND PhaseId=10
        AND FillinDate<='2019-07-20 00:00:00'
        AND FillinDate<='2019-07-21 23:59:59'
        AND IsApprove=1
        AND IsCancel IS NULL)

注意not exist：

转化为hive sqlÿ