MySQL中三表去重并集减另一表的SQL语句优化求助

你的核心需求其实很清晰：先拿到2016、2017、2018三张表的所有唯一邮箱，再把其中存在于2019表中的邮箱剔除。原SQL的问题出在你手动处理重复的逻辑太复杂，反而没覆盖到2017和2016之间的重复场景，其实MySQL的UNION关键字本身就能帮你自动去重，完全不用额外加WHERE NOT EXISTS来手动过滤。

正确的实现思路 #

1. 合并并去重：用UNION（不是UNION ALL，后者不会去重）合并三张表的邮箱，它会自动去除所有重复的邮箱值，不管重复来自哪两张表。
2. 剔除2019的邮箱：通过左连接或者NOT EXISTS过滤掉存在于2019表中的邮箱。
3. 生成结果序号：用ROW_NUMBER()函数生成结果表中的自增No列。

方法一：UNION + LEFT JOIN（直观易懂） #

SELECT 
    ROW_NUMBER() OVER () AS No,
    combined.email
FROM (
    -- 合并三张表的邮箱，UNION自动去重
    SELECT email FROM y2016
    UNION
    SELECT email FROM y2017
    UNION
    SELECT email FROM y2018
) AS combined
-- 左连接2019表，找出不在2019里的邮箱
LEFT JOIN y2019 ON combined.email = y2019.email
WHERE y2019.email IS NULL
ORDER BY No;

方法二：UNION + NOT EXISTS（性能更优，适合大表） #

如果你的邮箱字段有索引，用NOT EXISTS的查询性能会更好：

SELECT 
    ROW_NUMBER() OVER () AS No,
    combined.email
FROM (
    SELECT email FROM y2016
    UNION
    SELECT email FROM y2017
    UNION
    SELECT email FROM y2018
) AS combined
WHERE NOT EXISTS (
    SELECT 1 FROM y2019 WHERE y2019.email = combined.email
)
ORDER BY No;

原SQL的问题分析 #

你原来的SQL在UNION时给2017和2016表加了WHERE NOT EXISTS(SELECT * FROM y2018 ...)，这只能过滤掉和2018重复的邮箱，但没办法处理2017和2016之间的重复（比如brens@gmail.com在2017和2016都存在），导致合并后还有重复数据。而UNION会自动帮我们处理所有跨表的重复，完全不需要手动写这些过滤条件。

另外，原SQL里选择了*（包括原表的No列），但结果表的No是重新生成的自增序号，所以只需要选择email字段即可，避免不必要的字段干扰。

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