不能在JOIN ON里直接写CASE WHEN，因多数数据库会报语法错误或导致索引失效、全表扫描；应改用子查询预计算join_key、多LEFT JOIN加条件过滤，或映射表方案。
为什么不能在 JOIN ON 里直接写 CASE WHEN
因为绝大多数数据库（PostgreSQL、MySQL 8.0+、SQL Server）会直接报语法错误，或者即使能执行，也会让优化器彻底放弃索引和下推能力。典型表现是执行计划里出现 Nested Loop 外层套 Filter: (CASE ...)，说明两表先全量关联完，再逐行判断逻辑——这等于把 JOIN 变成了带条件的笛卡尔积。
常见错误现象包括：
-
ON t1.id = CASE WHEN t1.type = 'A' THEN t2.a ELSE t2.b END—— 直接语法报错或静默变慢 - JOIN 耗时暴涨，内存 OOM，EXPLAIN 显示
Rows Removed by Filter占比超 95% - 原本走索引的字段（如
t2.a）在 CASE 后无法命中，强制全表扫描
用子查询预计算 join_key 替代 ON 中的 CASE
这是兼容性最好、最易理解的解法：把动态逻辑收进子查询，生成一个统一类型的 join_key 字段，再做标准等值 JOIN。
实操要点：
- 子查询必须显式声明别名（如
o2），否则旧版 MySQL 会报Unknown column in ON clause -
CASE WHEN所有分支返回值类型必须一致，比如都为INT或都为VARCHAR(32)；混用'1'和1会触发隐式转换，拖慢 JOIN - 务必写
ELSE NULL，避免CASE返回意外值导致误匹配 - 如果原逻辑有 fallback（如“找不到就用 backup_id”），在外层用
COALESCE(u.id, o.backup_id)拉平，别塞回 ON
示例：
SELECT o.*, u.name
FROM orders o
JOIN (
SELECT id,
CASE WHEN order_type = 'direct' THEN user_id
WHEN order_type = 'agent' THEN agent_id
ELSE NULL
END AS join_key
FROM orders
) o2 ON o.id = o2.id
JOIN users u ON u.id = o2.join_key;
用多个 LEFT JOIN + 条件过滤模拟动态路由
当“该连哪张表”由左表字段决定（比如 user.type = 'vip' 就连 vip_orders），且右表结构不同时，这种写法比子查询更高效，也更容易加索引。
关键约束：
- 每个
LEFT JOIN的ON必须同时包含业务条件和关联字段，例如ON u.type = 'vip' AND u.order_id = vo.id—— 缺少u.type = 'vip'会导致交叉匹配 - 右表字段类型要对齐，否则
COALESCE(vo.amount, no.amount)可能因隐式转换失败 - 为
(type, order_id)这类组合条件建联合索引，避免单字段索引失效 - 如果右表数据量大，两个
LEFT JOIN会各自扫描一遍，注意评估 I/O 成本
示例：
SELECT u.id,
COALESCE(vo.amount, no.amount) AS order_amount
FROM users u
LEFT JOIN vip_orders vo ON u.type = 'vip' AND u.order_id = vo.id
LEFT JOIN normal_orders no ON u.type != 'vip' AND u.order_id = no.id;
映射表 + LEFT JOIN 是长期可维护的首选
当 CASE WHEN 实际是“代码→标签”“类型→字段名”这类静态映射关系（比如 status_code → status_name，或 order_type → target_table），硬编码在 SQL 里就是技术债。映射表能被索引、缓存、复用，JOIN 后还能用于其他查询。
必须检查的点：
- 映射表的
type_code字段必须有主键或唯一索引，否则LEFT JOIN可能产生重复行 - 原 CASE 有优先级（WHEN 顺序即优先级），映射表需加
sort_order字段，并在外层用ROW_NUMBER() OVER (PARTITION BY key ORDER BY sort_order)去重 - 用
ON t1.type = m.type_code AND m.label IS NOT NULL显式排除禁用项，别依赖ELSE - 外层
WHERE m.label IS NOT NULL会把左表无匹配的记录全过滤掉，要用LEFT JOIN就别在 WHERE 里筛右表字段
真正容易被忽略的是字段类型一致性——哪怕只是 VARCHAR 长度不同（VARCHAR(10) vs VARCHAR(100)），也可能让整列升格为 TEXT，JOIN 性能断崖下跌。这不是 bug，是多数数据库的类型推导规则。
