SQL统计A∩B、A-B、B-A中ID的Value计数，求更优实现方案

表结构 #

表A（仅ID列表） #

ID
---
1
5
9
...

表B（含ID和Y/N类型Value字段） #

ID   Value
----------
1      0
2      1
3      0
...

统计需求 #

需要统计以下三类ID对应的Value计数：

• 同时存在于表A和表B中的ID；
• 存在于表A但不存在于表B中的ID；
• 存在于表B但不存在于表A中的ID。

当前实现方式 #

采用UNION拼接查询结果：

SELECT Value, COUNT(*) AS count FROM tableA INNER JOIN tableB USING (id) GROUP BY Value
UNION 
SELECT Value, COUNT(*) AS count FROM tableA RIGHT JOIN tableB USING (id) WHERE tableA.id IS NULL GROUP BY Value

请问是否有更优的实现方式？

当然有更简洁高效的实现方式，核心思路是用一次关联查询覆盖所有三类情况，避免多次扫描表和UNION拼接。

方案1：全外连接直接统计（支持FULL OUTER JOIN的数据库，如PostgreSQL、SQL Server） #

SELECT
    CASE
        WHEN a.id IS NOT NULL AND b.id IS NOT NULL THEN '同时存在于A和B'
        WHEN a.id IS NOT NULL AND b.id IS NULL THEN '仅存在于A'
        ELSE '仅存在于B'
    END AS id_category,
    b.Value,
    COUNT(*) AS count
FROM tableA a
FULL OUTER JOIN tableB b ON a.id = b.id
GROUP BY id_category, b.Value;

这个查询通过CASE语句直接标记出每类ID的归属，一次扫描两张表就能完成所有统计，还能明确区分三类情况——原方案其实没覆盖“仅存在于A”的统计，这个方案直接补全了需求。

方案2：兼容无全外连接的数据库（如MySQL） #

如果你的数据库不支持FULL OUTER JOIN，可以先通过UNION ALL收集所有ID，再关联统计：

WITH all_ids AS (
    SELECT id FROM tableA
    UNION ALL
    SELECT id FROM tableB
)
SELECT
    CASE
        WHEN EXISTS(SELECT 1 FROM tableA a WHERE a.id = all_ids.id) 
             AND EXISTS(SELECT 1 FROM tableB b WHERE b.id = all_ids.id) THEN '同时存在于A和B'
        WHEN EXISTS(SELECT 1 FROM tableA a WHERE a.id = all_ids.id) THEN '仅存在于A'
        ELSE '仅存在于B'
    END AS id_category,
    (SELECT Value FROM tableB b WHERE b.id = all_ids.id) AS Value,
    COUNT(*) AS count
FROM all_ids
GROUP BY id_category, Value;

对比原方案的优势 #

• 原方案需要两次扫描表+UNION合并结果，新方案只需要1-2次扫描，性能更优；
• 逻辑更集中，所有统计规则在一个查询里完成，后期改需求更方便；
• 完整覆盖了三类ID的统计需求，原方案漏掉了“仅存在于A”的情况。

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