SQL技术求助：Union查询仅返回部分结果，如何找出跨表不匹配记录

嘿，我来帮你排查一下这个问题～你的需求是找出两列之间的不匹配记录，但当前SQL的括号位置写错了，导致逻辑完全不符合预期。

问题出在哪里？ #

你原来的语句结构是这样的：

select iNum from doc_file_fs where iNum not in (select iFile from doc_client_fs) 
union 
(select iFile from doc_employee_fs) 
union 
(select iFile from doc_prov_fs) 
UNION 
(select iFile from doc_soc_fs) 
UNION 
(select iFile from doc_cor_fs)

这里的not in只包含了第一个子查询（select iFile from doc_client_fs），后面的union是把第一个查询的结果和其他表的所有iFile直接合并，而不是把所有其他表的iFile都纳入“不匹配”的判断范围。这就导致你只能得到doc_file_fs中不在doc_client_fs里的iNum，再加上其他表的全部iFile，完全不是你想要的“双向不匹配”结果。

正确的写法（双向不匹配查询） #

假设你的需求是：

1. 找出doc_file_fs的iNum，不存在于其他所有表（doc_client_fs/doc_employee_fs等）的iFile列中的值；
2. 找出其他所有表的iFile，不存在于doc_file_fs的iNum列中的值。

可以用下面的SQL实现，还会给每条记录标注来源，方便你定位：

-- 第一部分：doc_file_fs中不在其他表iFile里的记录
select 
    iNum as mismatch_value,
    '来自doc_file_fs，未在其他表找到匹配' as source_info
from doc_file_fs
where iNum not in (
    select iFile from doc_client_fs
    union
    select iFile from doc_employee_fs
    union
    select iFile from doc_prov_fs
    union
    select iFile from doc_soc_fs
    union
    select iFile from doc_cor_fs
)

union all -- 用union all比union高效，因为不需要去重，如果你需要去重可以换成union

-- 第二部分：其他表中不在doc_file_fs的iNum里的记录
select 
    iFile as mismatch_value,
    '来自doc_client_fs，未在doc_file_fs找到匹配' as source_info
from doc_client_fs
where iFile not in (select iNum from doc_file_fs)

union all

select 
    iFile as mismatch_value,
    '来自doc_employee_fs，未在doc_file_fs找到匹配' as source_info
from doc_employee_fs
where iFile not in (select iNum from doc_file_fs)

union all

select 
    iFile as mismatch_value,
    '来自doc_prov_fs，未在doc_file_fs找到匹配' as source_info
from doc_prov_fs
where iFile not in (select iNum from doc_file_fs)

union all

select 
    iFile as mismatch_value,
    '来自doc_soc_fs，未在doc_file_fs找到匹配' as source_info
from doc_soc_fs
where iFile not in (select iNum from doc_file_fs)

union all

select 
    iFile as mismatch_value,
    '来自doc_cor_fs，未在doc_file_fs找到匹配' as source_info
from doc_cor_fs
where iFile not in (select iNum from doc_file_fs);

额外提示：避免not in的陷阱 #

如果你的子查询结果中包含NULL值，not in会返回空结果（因为SQL中NULL和任何值比较都是未知）。这种情况下，用not exists会更安全，比如把第一部分改成：

select 
    iNum as mismatch_value,
    '来自doc_file_fs，未在其他表找到匹配' as source_info
from doc_file_fs df
where not exists (
    select 1
    from (
        select iFile from doc_client_fs
        union
        select iFile from doc_employee_fs
        union
        select iFile from doc_prov_fs
        union
        select iFile from doc_soc_fs
        union
        select iFile from doc_cor_fs
    ) other_tables
    where other_tables.iFile = df.iNum
);

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