最好在“select-in”部分连接这两个表，并过滤掉join键上不为null的行。

CREATE TABLE admin.FctPrfitAmt_rpt AS 
    SELECT rcn.* FROM 
    (
        SELECT t1.* 
        FROM admin.FctPrfitAmt t1 
        LEFT JOIN admin.FctPrfitAmt_incr t2
        ON t1.scenario_id = t2.scenario_id
        WHERE t2.scenario_id IS NULL
        UNION ALL
        SELECT * FROM admin.FctPrfitAmt_incr
    ) rcn
;

select  *

from   (select  *
               ,max(tab) over (partition by scenario_id) as max_tab

        from    (           select *,1 as tab from master.FctPrfitAmt
                union all   select *,2 as tab from master.FctPrfitAmt_incr
                ) t
        ) t

where   tab     = 2
     or max_tab = 1
;

如果您的所有数据都由基本类型组成（没有数组、Map等），
您可以使用以下查询：

select  inline(array(original_rowset))

from   (select  original_rowset
               ,tab
               ,max(tab) over (partition by scenario_id) as max_tab

        from    (           select struct(*) as original_rowset,scenario_id,1 as tab from FctPrfitAmt
                union all   select struct(*) as original_rowset,scenario_id,2 as tab from FctPrfitAmt_incr
                ) t
        ) t

where   tab     = 2
     or max_tab = 1

你的语法错了。 NOT EXISTS 不应在前面加上 t2.scenario_id 正如我们所看到的，场景id在两个表上都是倾斜的，它在连接上创建了一个巨大的乘积。

select  * 
from    admin.FctPrfitAmt   pa
where   not exists 
        (
            select  null

            from   (select  distinct 
                            pfa.scenario_id 

                    from    admin.FctPrfitAmt_incr  pfa
                    ) pfa

            where   pfa.scenario_id = 
                    pa.scenario_id
        )

union all

select  * 
from    admin.FctPrfitAmt_incr