ON子句不能写子查询，因执行逻辑要求其必须是可索引的等值/范围匹配条件；子查询结果不可预估、无法下推，优化器直接拒绝，需通过派生表、CTE等方式物化后JOIN。
ON 子句里不能写子查询，不是语法限制，而是执行逻辑不允许——它根本没法做等值匹配。
ON 本质是“连接条件”，不是“过滤表达式”
数据库在执行 JOIN 时，会把 ON 当作两表之间建立关联的“键映射规则”。它必须能快速判断某行 a 是否该和某行 b 连上，这就要求 ON 条件最终能归结为一个确定的、可索引的等值或范围比较（比如 a.id = b.a_id）。子查询（如 a.id IN (SELECT user_id FROM logs)）无法满足这个前提：它的结果集不可预估、不可下推到连接算法中，优化器干脆拒绝解析。
- MySQL/PostgreSQL 都会直接报错，例如 MySQL 的
ERROR 1054 (42S22): Unknown column ... in 'on clause'或更明确的Subquery is not allowed in this context - 即使某些方言（如旧版 SQLite）允许，实际执行也会退化成嵌套循环 + 每次调用子查询，性能崩盘
- ON 中允许的只有列名、常量、函数调用（如
COALESCE(b.status, 'inactive')），但前提是这些函数不引入不可下推逻辑
想实现“用子查询结果来驱动连接”，得先物化它
真正要做的，不是“在 ON 里塞子查询”，而是把子查询的结果提前算出来，变成一张临时可用的“右表”。这叫物化（materialization），常见做法有三种：
- 用派生表：
LEFT JOIN (SELECT user_id, COUNT(*) AS cnt FROM logs GROUP BY user_id) l ON u.id = l.user_id—— 最通用，所有主流数据库都支持 - 用 CTE（推荐 PostgreSQL / MySQL 8.0+）：
WITH log_stats AS (SELECT user_id, COUNT(*) cnt FROM logs GROUP BY user_id) SELECT u.*, l.cnt FROM users u LEFT JOIN log_stats l ON u.id = l.user_id - 建临时表或物化视图（适合高频复用场景），但要注意权限与刷新策略
关键点：物化后，JOIN 的右端就变成了一个“有明确结构、可走索引”的表，ON 才能正常工作。
容易忽略的 NULL 和重复问题
把子查询改写成 JOIN 后，语义可能悄悄变了：
- 原
WHERE id IN (SELECT user_id FROM logs)遇到logs.user_id为空或全 NULL，整个条件为 FALSE；而LEFT JOIN ... ON u.id = l.user_id WHERE l.user_id IS NOT NULL才等价于EXISTS，漏掉IS NOT NULL就会多出 NULL 行 - 如果子查询没
GROUP BY却返回多行匹配（比如SELECT user_id FROM logs），JOIN 会放大左表行数——必须确认业务是否允许重复，否则得加DISTINCT或提前去重 -
COALESCE要手动补：JOIN 物化表后，没匹配上的字段是NULL，不是 0 或空字符串，COALESCE(l.cnt, 0)这类处理不能省
最麻烦的不是写法，而是验证。每次改写后必须用 EXPLAIN 对比执行计划，重点看 type 是否从 ALL 变成 ref 或 eq_ref，以及 rows 预估是否显著下降——否则很可能只是换了一种慢法。
