不合理。case when不能直接用于join的on子句，因其属运行时表达式，而join类型需编译期确定，会导致语法错误、优化失效及索引失效；应改用多个left join+状态过滤、union all拆分或映射表等方案。
JOIN条件里不能直接写CASE WHEN？先搞清执行顺序
SQL的JOIN发生在WHERE之前，而CASE WHEN属于表达式计算阶段，不能直接当JOIN的“连接谓词”用。很多人写ON t1.id = CASE WHEN t2.status = 'A' THEN t2.ref_id ELSE t2.alt_id END，结果发现逻辑错乱——不是语法报错，而是语义不对：它强制所有行都尝试匹配t2.ref_id或t2.alt_id，但实际业务中，状态为'B'的记录根本不该走ref_id这条路。
关键点在于：动态JOIN的本质是“按状态决定要不要连、连哪张表、连哪个字段”，不是“在同一个ON里切字段”。
用UNION ALL + 固定JOIN拆解不同状态路径
这是最可控、可读性最强的做法。把每种业务状态视为独立查询分支，各自写明确的JOIN逻辑，最后合并：
SELECT t1.*, t2.name AS ref_name, NULL AS alt_name FROM orders t1 JOIN customers t2 ON t1.ref_id = t2.id WHERE t1.status = 'confirmed' <p>UNION ALL</p><p>SELECT t1.*, NULL AS ref_name, t3.name AS alt_name FROM orders t1 JOIN partners t3 ON t1.alt_id = t3.id WHERE t1.status = 'pending'</p>
注意三点：
- 每个分支的SELECT列数、类型、别名必须严格一致，否则
UNION ALL失败 -
WHERE过滤必须写在每个分支内部，不能提到外面，否则会漏掉状态隔离逻辑 - 如果要保留原始
orders所有行（包括未匹配状态），就把对应分支改成LEFT JOIN，并确保NULL值能被业务接受
用LEFT JOIN配合COALESCE + 状态过滤做“软连接”
适合状态不多、且主表必须全量保留的场景。核心思路是：对每个可能关联的目标字段，单独LEFT JOIN一次，再用COALESCE选值：
SELECT
t1.*,
COALESCE(c.name, p.name) AS linked_name,
CASE
WHEN t1.status = 'confirmed' THEN c.name
WHEN t1.status = 'pending' THEN p.name
END AS explicit_name
FROM orders t1
LEFT JOIN customers c ON t1.status = 'confirmed' AND t1.ref_id = c.id
LEFT JOIN partners p ON t1.status = 'pending' AND t1.alt_id = p.id
这里的关键是把状态判断提前到ON条件里：t1.status = 'confirmed' AND t1.ref_id = c.id。这样，当t1.status不是'confirmed'时，这个JOIN自动不生效（不会引入额外行），避免笛卡尔积。
容易踩的坑：
- 忘记在
ON里加状态条件，只写t1.ref_id = c.id→ 所有行都去匹配customers，性能爆炸 - 用
INNER JOIN代替LEFT JOIN→ 状态不匹配的订单直接被丢弃 -
COALESCE(c.name, p.name)在两个都为NULL时返回NULL，但业务上可能需要区分“没配对”和“配对失败”
为什么不用子查询或LATERAL？看数据库支持度
PostgreSQL支持LATERAL，可以写：
SELECT t1.*, link.name FROM orders t1, LATERAL ( SELECT name FROM customers WHERE t1.status = 'confirmed' AND id = t1.ref_id UNION ALL SELECT name FROM partners WHERE t1.status = 'pending' AND id = t1.alt_id ) AS link
但MySQL 8.0.14+才支持LATERAL，SQL Server用APPLY，SQLite不支持。所以除非团队统一栈且版本可控，否则优先选前两种方案。
真正麻烦的从来不是语法，而是状态定义是否稳定——比如新增一个'archived'状态，你得同步改三处：UNION分支、LEFT JOIN条件、COALESCE分支。状态枚举散落在SQL里，比写死更危险。
