SQL LEFT JOIN实现区域匹配TRUE/FALSE列并解决重复行问题

现有两张业务表：

• 商品表item_table：仅含item_id字段，存储值范围为1-5
• 区域关联表region_table：包含item_id、region（区域名称）、region_id（区域ID）三个字段
  期望输出规则：
• 结果覆盖所有出现过的item_id，包含仅在区域表存在的item_id=6
• 每个item_id仅返回1行数据
• 将A、B、C、D、E五个region_id转换为独立列，商品匹配到对应区域时列值为TRUE，未匹配则为FALSE

之前的LEFT JOIN写法无法得到正确结果，本质是三个典型问题：

1. 表关联后未做聚合处理，一个商品绑定多个区域时会返回多行重复数据
2. CASE WHEN判断逻辑写反，匹配/未匹配区域对应的布尔值颠倒
3. WHERE子句中添加了右表字段的过滤条件，直接破坏左连接的外连接特性，会漏掉无匹配区域的商品、以及仅在区域表存在的商品ID

1. 先通过UNION合并两张表的item_id，取去重后的全量商品ID集合，从数据源层面保证不遗漏任何ID
2. 用全量ID集合左连区域关联表，所有右表的过滤条件统一写在JOIN ON子句中，避免外连接失效
3. 用GROUP BY + 条件聚合的方式做行转列，保证每个商品ID仅返回一行，同时准确输出对应区域的匹配布尔值

WITH all_item AS (
    -- 合并两张表的所有item_id，去重后得到全量ID集合
    SELECT item_id FROM item_table
    UNION
    SELECT item_id FROM region_table
)
SELECT 
    ai.item_id,
    MAX(CASE WHEN rt.region_id = 'A' THEN TRUE ELSE FALSE END) AS region_A,
    MAX(CASE WHEN rt.region_id = 'B' THEN TRUE ELSE FALSE END) AS region_B,
    MAX(CASE WHEN rt.region_id = 'C' THEN TRUE ELSE FALSE END) AS region_C,
    MAX(CASE WHEN rt.region_id = 'D' THEN TRUE ELSE FALSE END) AS region_D,
    MAX(CASE WHEN rt.region_id = 'E' THEN TRUE ELSE FALSE END) AS region_E
FROM all_item ai
LEFT JOIN region_table rt 
    ON ai.item_id = rt.item_id
GROUP BY ai.item_id
ORDER BY ai.item_id;

• 不要直接用商品表左连区域表，会漏掉仅在区域表存在的item_id=6
• 行转列必须搭配GROUP BY聚合，否则一个商品对应多个区域时必然出现重复行
• 左连接场景下，右表的过滤条件必须写在JOIN ON子句中，写在WHERE里会等价于内连接，丢失未匹配的行
• 写完CASE WHEN后要核对逻辑，避免把TRUE/FALSE的返回顺序写反

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