SQL通过公共isbn列全外连接合并两表出现null列问题求解

问题原因 #

你的SQL存在两处明显问题：

• 使用SELECT *会同时返回左右两个子查询的isbn列，最终结果出现重复的isbn字段，关联未命中侧的isbn自然显示为null
• 没有对关联后产生的null计数做补0处理，也没有将两侧的isbn合并为单个公共列

正确实现方案 #

方案1：修正全外连接写法（适用于PostgreSQL、Oracle、SQL Server等支持FULL OUTER JOIN的数据库） #

使用COALESCE函数合并两侧isbn列，同时将null值的计数替换为0即可：

SELECT
  COALESCE(t1.isbn, t2.isbn) AS isbn,
  COALESCE(t2.in_count, 0) AS in_count,
  COALESCE(t1.out_count, 0) AS out_count
FROM
(SELECT isbn, COUNT(*) AS out_count FROM out_tbl GROUP BY isbn) t1
FULL OUTER JOIN
(SELECT isbn, COUNT(*) AS in_count FROM in_tbl GROUP BY isbn) t2
ON t1.isbn = t2.isbn;

说明：COALESCE会返回入参列表中第一个非null的值，刚好适配全外连接两侧字段互补的场景。

方案2：UNION合并ISBN后左关联（全数据库兼容，适用于不支持FULL OUTER JOIN的场景如低版本MySQL） #

先把两张表的所有isbn去重合并成全集，再分别左关联两个表的聚合统计结果，不存在的计数补0：

-- 先取出两表所有去重后的isbn集合
WITH all_isbn AS (
  SELECT isbn FROM in_tbl
  UNION
  SELECT isbn FROM out_tbl
)
SELECT
  a.isbn,
  IFNULL(i.in_count, 0) AS in_count,
  IFNULL(o.out_count, 0) AS out_count
FROM all_isbn a
LEFT JOIN (SELECT isbn, COUNT(*) AS in_count FROM in_tbl GROUP BY isbn) i 
  ON a.isbn = i.isbn
LEFT JOIN (SELECT isbn, COUNT(*) AS out_count FROM out_tbl GROUP BY isbn) o 
  ON a.isbn = o.isbn;

补充注意 #

如果你的出入库表中每行记录的in_count/out_count是单次出入库的数量（而非每行固定代表1个单位的出入库），需要将聚合逻辑从COUNT(*)替换为SUM(对应计数字段)，例如入库统计子查询改写为SELECT isbn, SUM(in_count) AS in_count FROM in_tbl GROUP BY isbn，统计结果才会准确。

内容的提问来源于stack exchange，提问作者Đức Trường Đặng