in子查询在mysql 5.6+中可优化为semi-join，性能媲美join；exists适用于小外层大内层且只需存在性判断的场景；静态小in列表效率最高；join易引发结果集膨胀，应按需选择。
IN子查询被优化为semi-join时性能不输JOIN
MySQL 5.6+ 对满足条件的 IN 子查询会自动重写为半连接（semi-join），执行路径与等价 INNER JOIN 几乎一致，EXPLAIN 中常看到 type: eq_ref 或 Using join buffer。这种情况下，语法差异不再带来性能差距。
触发自动 semi-join 的关键条件包括：
-
IN子查询是非相关的（不引用外层表字段） - 子查询不含
GROUP BY、ORDER BY、LIMIT、聚合函数或DISTINCT - 子查询返回字段有索引（最好是覆盖索引）
- 子查询结果集不大（优化器估算成本低）
例如：SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE status = 'paid')，只要 orders(status, user_id) 有复合索引，大概率走 semi-join，无需手动改写。
EXISTS短路机制在小外层+大内层场景下更轻量
当外层表行数少、内层表巨大且存在高效索引时，EXISTS 往往比 JOIN 更快——它找到第一个匹配就停，不构造中间结果集。
典型适用场景：
- 外层主表仅几百或几千行（如配置表、状态表）
- 内层表有复合索引能支撑快速跳查（如
(ref_id, created_at)） - 语义是“是否存在”，而非“取关联字段”
对比 JOIN：即使加 DISTINCT，也可能触发 Using temporary 和 Using filesort；而 EXISTS 天然无重复、无膨胀，执行计划更干净。
IN列表值极少时，硬编码比JOIN更省资源
当 IN 后是静态小列表（如 IN (1, 2, 5, 8)），数据库直接走常量哈希查找，连索引都不用，比建临时表再 JOIN 少了元数据开销和连接逻辑。
这类写法在以下情况更优：
- 值数量 ≤ 100，且基本不变（如状态码、类型枚举）
- 应用层能安全拼接（注意SQL注入防护）
- 目标数据库对常量
IN有深度优化（PostgreSQL 做哈希过滤，SQL Server 转为位图）
强行改成 JOIN (VALUES (1),(2),(5),(8)) AS v(id) 反而增加解析和计划生成负担。
JOIN引发中间结果集膨胀反而拖慢查询
JOIN 的本质是组合行，一旦一对多关系未加限制，中间结果集可能指数级膨胀。比如 1 万用户 × 平均 300 订单 = 300 万行，后续 GROUP BY 或 ORDER BY 就极易触发磁盘临时表。
而等效的 IN 或 EXISTS 只返回布尔判断或主表原始行，内存占用低、排序压力小。
容易忽略的关键点：
- 是否真需要关联字段？如果只校验存在性，
EXISTS比JOIN更精准 - 子查询是否已带有效过滤？比如
WHERE ... IN (SELECT id FROM logs WHERE type = 'error' AND dt > NOW() - INTERVAL 1 DAY)，先筛再判，比JOIN后WHERE更早剪枝 -
NOT IN必须警惕NULL——它会导致整个条件为UNKNOWN，结果为空，应一律换NOT EXISTS
