SQL无需UNION实现条件连接取值的解决方案咨询

原有查询使用INNER JOIN同时关联type_1和type_2表，而things表每行固定仅t1_id/t2_id其中一个非空，因此任意一行都无法同时满足两个INNER JOIN的关联条件，最终查询结果为空，无法返回全部4条预期数据。

将INNER JOIN替换为LEFT JOIN即可，无需使用UNION，后续处理t3_id/t4_id取name2的逻辑也可以完全复用：

SELECT COALESCE(t1.name, t2.name) AS name
FROM things
LEFT JOIN type_1 AS t1 ON things.t1_id = t1.id
LEFT JOIN type_2 AS t2 ON things.t2_id = t2.id
-- 无重复name可删除GROUP BY
GROUP BY name;

逻辑说明：LEFT JOIN会保留things表的所有行，即使关联的类型表无匹配也不会过滤行，COALESCE会自动取非空的name值，刚好匹配业务规则。后续需要取第二列name2时，只需新增两个LEFT JOIN关联type_3、type_4，再加COALESCE(t3.name, t4.name) AS name2即可，不会产生额外开销。

如果后续会持续新增更多关联类型，建议优化things表结构，避免每次加类型都新增字段：

1. 调整后的表结构

CREATE TABLE things (
  `row_id` INT PRIMARY KEY AUTO_INCREMENT COMMENT '原行标识',
  `ref_type` TINYINT COMMENT '关联类型：1=type_1,2=type_2,3=type_3,4=type_4',
  `ref_id` INT COMMENT '对应关联表主键',
  `ref_group` TINYINT COMMENT '关联分组：1对应第一列name，2对应第二列name2'
);

2. 查询示例

SELECT
  ref_group,
  CASE ref_type
    WHEN 1 THEN t1.name
    WHEN 2 THEN t2.name
    WHEN 3 THEN t3.name
    WHEN 4 THEN t4.name
  END AS name
FROM things
LEFT JOIN type_1 t1 ON ref_type = 1 AND ref_id = t1.id
LEFT JOIN type_2 t2 ON ref_type = 2 AND ref_id = t2.id
LEFT JOIN type_3 t3 ON ref_type = 3 AND ref_id = t3.id
LEFT JOIN type_4 t4 ON ref_type = 4 AND ref_id = t4.id
GROUP BY ref_group, name;

该方案后续新增关联类型无需修改表结构，只需新增ref_type枚举值即可，扩展性更强。

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