如何在PostgreSQL中通过JOIN统计两张同结构表的记录数

要实现这个需求，核心是先分别统计两张表中各id的记录数，再将两个统计结果关联，避免直接关联原表产生笛卡尔积导致计数错误。

方法一：标准SQL（支持FULL OUTER JOIN的数据库，如PostgreSQL、SQL Server等） #

先分别统计两张表的id计数，再通过FULL OUTER JOIN关联，确保即使某个id只在其中一张表存在，也能被统计到（不存在的表计数显示0）：

SELECT
    COALESCE(t1.id, t2.id) AS id,
    COALESCE(t1.itemcount, 0) AS table1.itemcount,
    COALESCE(t2.itemcount, 0) AS table2.itemcount
FROM
    (SELECT id, COUNT(*) AS itemcount FROM table1 GROUP BY id) t1
FULL OUTER JOIN
    (SELECT id, COUNT(*) AS itemcount FROM table2 GROUP BY id) t2
ON t1.id = t2.id
ORDER BY id;

方法二：MySQL兼容写法（MySQL不支持FULL OUTER JOIN） #

先通过UNION ALL获取所有唯一id，再分别左连接两张表的统计结果：

SELECT
    u.id,
    COALESCE(t1.itemcount, 0) AS table1.itemcount,
    COALESCE(t2.itemcount, 0) AS table2.itemcount
FROM
    (SELECT id FROM table1 UNION SELECT id FROM table2) u
LEFT JOIN
    (SELECT id, COUNT(*) AS itemcount FROM table1 GROUP BY id) t1 ON u.id = t1.id
LEFT JOIN
    (SELECT id, COUNT(*) AS itemcount FROM table2 GROUP BY id) t2 ON u.id = t2.id
ORDER BY id;

结果验证 #

用你提供的示例数据测试，两种方法都会输出：

关键说明 #

• 先分组统计再关联：避免直接关联原表时，同一id的多条记录交叉匹配，导致计数被错误放大。
• COALESCE函数：用来处理id只在一张表存在的情况，将NULL替换为0，保证结果的完整性。

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