标准sql禁止在join的on子句中直接使用case when，因其破坏优化器估算与执行计划稳定性；应改用子查询预生成统一关联键，或通过多个left join配合互斥条件实现分流。
JOIN 的 ON 子句里不能直接写 CASE WHEN
标准 SQL 明确禁止在 ON 子句中用 CASE WHEN 构造连接表达式，比如 ON t1.id = CASE WHEN t2.type = 'A' THEN t3.a_id ELSE t3.b_id END。PostgreSQL、MySQL 8.0+、SQL Server 都会报语法错误或拒绝执行——这不是兼容性问题，而是语义层面不被允许。
根本原因是：这类写法让优化器无法预估连接基数，破坏索引选择与执行计划稳定性，数据库干脆从语法层拦截。
- 所有想“动态决定关联哪一列”的尝试，只要写在
ON里带CASE WHEN，必报错 -
LEFT JOIN和INNER JOIN都一样受限，没有例外 - 某些旧版 MySQL（如 5.7）可能“看似能跑”，实则是解析器宽松导致的隐式转换，行为不可靠
用子查询预生成统一关联键
最通用、可读性强、全数据库兼容的做法：先把 CASE WHEN 放进子查询，输出一个干净的、类型一致的“逻辑键”，再用这个键做标准等值 JOIN。
适用场景：同一张左表记录，需按业务规则（如 order_type）分别关联不同字段（user_id 或 agent_id）。
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;
-
CASE WHEN所有分支必须返回相同类型（例如全为INT），否则JOIN可能静默失败或触发隐式转换 - 务必写
ELSE NULL，避免order_type出现未覆盖值时，join_key被强制转成非空类型，导致意外匹配 - 子查询别名（如
o2）不能省，否则旧版 MySQL 会报Unknown column in ON clause
多个 LEFT JOIN + 条件过滤替代
当“条件”本质是分流（比如只取用户或代理商之一），而不是构造新键，用多个 LEFT JOIN 更高效，也更贴近执行意图。
关键约束：每个 ON 必须同时包含业务判断和关联字段，且条件互斥、覆盖全集。
SELECT o.*,
COALESCE(u.name, a.name) AS contact_name
FROM orders o
LEFT JOIN users u ON o.order_type = 'direct' AND o.user_id = u.id
LEFT JOIN agents a ON o.order_type = 'agent' AND o.agent_id = a.id;
-
ON中的o.order_type = 'direct'不可省略，否则会拉出笛卡尔积 -
COALESCE顺序决定优先级，但两边字段类型必须一致（如都是VARCHAR），否则可能报错 - 如果
order_type有第三种值（如'system'），当前写法天然跳过关联，无需额外处理
WHERE 里引用右表字段会让 LEFT JOIN 失效
这是最隐蔽也最常踩的坑：写 WHERE u.status = 'active' 看似只是过滤，实际会把左表中所有右表为 NULL 的行全部剔除，LEFT JOIN 退化为 INNER JOIN。
正确做法是把右表筛选逻辑移进 ON：
- 错：
LEFT JOIN users u ON o.user_id = u.id WHERE u.status = 'active'→ 丢掉所有无用户或用户非 active 的订单 - 对：
LEFT JOIN users u ON o.user_id = u.id AND u.status = 'active'→ 保留所有订单，只关联 status 为 active 的用户 - 只要左表要全量保留，所有右表字段的筛选条件都必须进
ON，WHERE只该用于最终结果集的整体过滤
复杂点在于，一旦关联逻辑变多（比如嵌套字典表、状态映射），ON 里的条件容易堆叠失焦——这时宁可拆成子查询，也别牺牲可维护性。
