如何无需UNION合并两张表的差异查询结果（含value差值>10条件）

现有两张结构完全一致的表test.test和test.test1，需找出两类差异记录：

• 两表中month_date、level_1至level_4、level_header、unit、model_type_id、model_version_desc这9列完全匹配，但value列的绝对值差大于10；
• 两表中存在单边缺失的记录（限定month_date为'2022-11-01'）。

已分别通过INNER JOIN和FULL OUTER JOIN实现两类记录的查询，现需无需使用UNION，将两个查询结果合并为单个查询（允许使用子查询）。

原查询语句如下：

原查询1（value差值超标记录） #

select * 
from  test.test m 
inner join test.test1 t 
    on
m.month_date = t.month_date and  
m.level_1 = t.level_1 and
m.level_2 = t.level_2 and 
m.level_3 = t.level_3 and 
m.level_4 = t.level_4 and 
m.level_header = t.level_header and 
m.unit = t.unit and 
m.model_type_id = t.model_type_id and 
m.model_version_desc = t.model_version_desc 
where m.month_date = '2022-11-01' and abs(m.value - t.value)  > 10

（注：原语句末尾多了一个多余的右括号，已修正）

原查询2（单边缺失记录） #

select  *
from  test.test m 
full outer join test.test1 t 
    on
m.month_date = t.month_date and  
m.level_1 = t.level_1 and
m.level_2 = t.level_2 and 
m.level_3 = t.level_3 and 
m.level_4 = t.level_4 and 
m.level_header = t.level_header and 
m.unit = t.unit and 
m.model_type_id = t.model_type_id and 
m.model_version_desc = t.model_version_desc 
where (m.month_date is null  or t.month_date is null) and (m.month_date = '2022-11-01' or t.month_date = '2022-11-01')

（注：原语句WHERE条件逻辑有误，已修正括号和日期判断逻辑）

直接基于FULL OUTER JOIN，在WHERE条件中同时包含两类情况的判断即可，无需UNION：

select *
from test.test m
full outer join test.test1 t
    on m.month_date = t.month_date
    and m.level_1 = t.level_1
    and m.level_2 = t.level_2
    and m.level_3 = t.level_3
    and m.level_4 = t.level_4
    and m.level_header = t.level_header
    and m.unit = t.unit
    and m.model_type_id = t.model_type_id
    and m.model_version_desc = t.model_version_desc
where 
    -- 条件1：两表匹配但value差值超10，且日期为目标日期
    (
        m.month_date is not null 
        and t.month_date is not null
        and m.month_date = '2022-11-01'
        and abs(m.value - t.value) > 10
    )
    -- 条件2：单边缺失，且存在的那一侧日期为目标日期
    or 
    (
        (m.month_date is null or t.month_date is null)
        and coalesce(m.month_date, t.month_date) = '2022-11-01'
    )

1. 用FULL OUTER JOIN覆盖所有匹配、单边缺失的场景；
2. WHERE条件通过OR连接两类判断：
   • 第一部分筛选两边都有匹配记录，且日期符合要求、value差值超标的情况；
   • 第二部分筛选单边缺失的记录，通过coalesce函数取存在的那一侧的日期，确保其为'2022-11-01'；
3. 一次查询即可返回所有符合要求的差异记录。

内容的提问来源于stack exchange，提问作者Miko