如何找出两个子查询生成列中的不同元素？

先理清楚你的需求：从子查询返回的两列里，提取出只在其中一列出现、另一列没有的元素，也就是两个集合的对称差集。先看看你给出的样本数据：

子查询结果：

column1 column2
a       d
b       z1000
c       c
d       
1       
2       z1000
        k

column1的有效元素集合：{a,b,c,1,2,d,z1000,…}
column2的有效元素集合：{d,c,z1000,k,…}
期望结果：{a,k,1,2,…}

下面给你两种通用的实现方法，适配不同的SQL数据库：

方法一：兼容性拉满的通用写法（支持所有SQL数据库） #

这种写法不管你用MySQL、PostgreSQL还是SQL Server都能正常运行，核心思路是分别找出两列各自独有的元素，再合并结果：

-- 提取column1中存在但column2中没有的元素
SELECT DISTINCT column1 AS diff_element
FROM (
    -- 这里替换成你的实际子查询
    SELECT column1, column2 FROM your_table WHERE ...
) AS subquery
WHERE column1 IS NOT NULL
  AND NOT EXISTS (
    SELECT 1
    FROM (
        SELECT column1, column2 FROM your_table WHERE ...
    ) AS subquery2
    WHERE subquery2.column2 = subquery.column1
  )

UNION ALL

-- 提取column2中存在但column1中没有的元素
SELECT DISTINCT column2 AS diff_element
FROM (
    SELECT column1, column2 FROM your_table WHERE ...
) AS subquery
WHERE column2 IS NOT NULL
  AND NOT EXISTS (
    SELECT 1
    FROM (
        SELECT column1, column2 FROM your_table WHERE ...
    ) AS subquery2
    WHERE subquery2.column1 = subquery.column2
  );

• DISTINCT用来避免同一元素重复出现在结果里；
• UNION ALL比UNION更高效，因为我们已经在各自的查询里做过去重了；
• IS NOT NULL用来过滤掉空值，避免把空元素误判为有效集合成员。

方法二：简化写法（支持PostgreSQL、SQL Server等） #

如果你的数据库支持EXCEPT语法，可以用更简洁的方式实现：

-- 获取column1独有的元素
SELECT DISTINCT column1 AS diff_element
FROM (SELECT column1, column2 FROM your_table WHERE ...) AS subquery
WHERE column1 IS NOT NULL
EXCEPT
SELECT DISTINCT column2
FROM (SELECT column1, column2 FROM your_table WHERE ...) AS subquery
WHERE column2 IS NOT NULL

UNION

-- 获取column2独有的元素
SELECT DISTINCT column2 AS diff_element
FROM (SELECT column1, column2 FROM your_table WHERE ...) AS subquery
WHERE column2 IS NOT NULL
EXCEPT
SELECT DISTINCT column1
FROM (SELECT column1, column2 FROM your_table WHERE ...) AS subquery
WHERE column1 IS NOT NULL;

EXCEPT会自动帮我们筛选出前一个结果集有、后一个结果集没有的元素，UNION则负责把两部分差异结果合并并去重。

小提示 #

如果你的子查询比较复杂，可以先把它存入临时表，这样写SQL的时候更清晰，也能提升查询效率。

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