FULL OUTER JOIN基于两个等值条件未按预期生效问题排查

原查询的核心问题 #

1. 关联条件缺失产品维度：原JOIN仅通过campaign和subcampaign关联，未加入product字段，无法精准匹配对应产品的销售与成本数据，导致Table B中独有的产品记录无法正确映射。
2. 字段取值依赖左表：查询直接使用a.campaign、a.subcampaign、a.product，当Table B存在Table A没有的记录时，这些字段会返回NULL，导致数据无法正常显示。
3. 冗余的外层聚合：子查询已经完成了sales和cost的分组求和，外层无需再次执行SUM操作。

修正后的查询语句 #

SELECT
  COALESCE(a.campaign, b.campaign) AS campaign,
  COALESCE(a.subcampaign, b.subcampaign) AS subcampaign,
  COALESCE(a.product, b.product) AS product,
  a.sales,
  b.cost
FROM
  (
    SELECT
      campaign,
      subcampaign,
      product,
      SUM(sales) AS sales
    FROM table_a
    GROUP BY 1, 2, 3
  ) a
FULL OUTER JOIN
  (
    SELECT
      campaign,
      subcampaign,
      product,
      SUM(cost) AS cost
    FROM table_b
    GROUP BY 1, 2, 3
  ) b
ON
  a.campaign = b.campaign
  AND a.subcampaign = b.subcampaign
  AND a.product = b.product

原表结构 #

Table A

Table B

期望结果 #

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