MySQL对比两个查询结果列的差异值，临时表报错求可行方案

问题原因 #

MySQL临时表存在限制：在同一条SQL语句中，临时表只能被引用一次。原代码中UNION的两个分支都对临时表进行了子查询引用，触发了cannot reopen temporary table错误。

替代方案 #

方案1：使用CTE（公共表表达式）

MySQL 8.0及以上版本支持CTE，写法简洁且无需创建临时表：

WITH cte_a AS (
    SELECT number FROM table1 WHERE my_condition
), cte_b AS (
    SELECT number FROM table2 WHERE my_condition
)
SELECT number FROM cte_a WHERE number NOT IN (SELECT number FROM cte_b)
UNION
SELECT number FROM cte_b WHERE number NOT IN (SELECT number FROM cte_a);

方案2：直接嵌入子查询

无需创建临时表，将查询A和B直接作为子查询嵌入主语句：

SELECT number FROM (SELECT number FROM table1 WHERE my_condition) AS a
WHERE number NOT IN (SELECT number FROM table2 WHERE my_condition)
UNION
SELECT number FROM (SELECT number FROM table2 WHERE my_condition) AS b
WHERE number NOT IN (SELECT number FROM table1 WHERE my_condition);

方案3：LEFT JOIN + IS NULL（处理NULL值更安全）

如果number字段可能存在NULL值，NOT IN会导致结果异常，使用LEFT JOIN的方式更可靠：

-- 筛选A存在但B不存在的数值
SELECT a.number
FROM (SELECT number FROM table1 WHERE my_condition) AS a
LEFT JOIN (SELECT number FROM table2 WHERE my_condition) AS b 
    ON a.number = b.number
WHERE b.number IS NULL

UNION

-- 筛选B存在但A不存在的数值
SELECT b.number
FROM (SELECT number FROM table2 WHERE my_condition) AS b
LEFT JOIN (SELECT number FROM table1 WHERE my_condition) AS a 
    ON b.number = a.number
WHERE a.number IS NULL;

方案4：UNION ALL + GROUP BY（高效去重对比）

通过合并两个结果集，统计每个数值的出现次数，仅保留出现一次的数值（即只在其中一个集合存在的数值）：

SELECT number
FROM (
    SELECT number FROM table1 WHERE my_condition
    UNION ALL
    SELECT number FROM table2 WHERE my_condition
) AS combined
GROUP BY number
HAVING COUNT(*) = 1;

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