LEFT JOIN 中嵌套 IN 子查询慢，因优化器难将其转为高效连接路径，常强制物化子查询、逐行匹配，引发高 I/O 与 CPU 开销；正确做法是将 IN 条件下沉至 ON 子句并建立复合索引。
LEFT JOIN 里嵌套 IN 子查询为什么慢
因为数据库优化器通常无法将 IN 子查询与 LEFT JOIN 合并为高效连接路径，尤其当子查询含聚合、GROUP BY 或非确定性函数（如 GETDATE()）时，IN 会被强制物化成临时结果集，再逐行匹配左表——这等价于多次执行子查询，I/O 和 CPU 开销陡增。
常见错误现象包括：Execution plan 中出现 Table Scan 或 Index Scan 而非 Index Seek，rows 预估远高于实际，且 Warnings 栏提示 “Null value eliminated in set function” 或 “Convert warning”。
- 子查询返回 NULL 时，
IN整体结果为 UNKNOWN，导致 LEFT JOIN 的右表匹配逻辑混乱 -
IN (SELECT ...)在 SQL Server 中默认不走并行计划，而等价的LEFT JOIN更容易被优化器选中哈希连接（Hash Join） - 如果子查询涉及大表且无合适索引，
IN会先全量扫描子查询表，再反向过滤左表；而LEFT JOIN可借助驱动表选择和连接下推（如 SQL Server 2016+ 的Batch Mode on Rowstore）降低中间结果集大小
用 LEFT JOIN 替代 IN 的正确写法
核心是把原 IN 子查询的 WHERE 条件“下沉”到 ON 子句，而不是留在 WHERE；否则语义就变成 INNER JOIN，丢失左表空匹配行。
示例：原语句
SELECT o.OrderId, o.Amount FROM Orders o WHERE o.UserId IN ( SELECT u.UserId FROM Users u WHERE u.Status = 'Active' );
应改写为：
SELECT o.OrderId, o.Amount FROM Orders o LEFT JOIN Users u ON o.UserId = u.UserId AND u.Status = 'Active' WHERE u.UserId IS NOT NULL;
-
AND u.Status = 'Active'必须在ON中，不能挪到WHERE，否则u.Status = 'Active'会过滤掉所有u.UserId IS NULL行，失去 LEFT JOIN 意义 - 若需保留所有
Orders行（含无匹配用户者），则WHERE u.UserId IS NOT NULL改为WHERE u.UserId IS NULL或直接删掉该条件 - 确保
Users(UserId, Status)上有复合索引，否则LEFT JOIN仍可能退化为扫描
IN 子查询含聚合或 DISTINCT 时怎么处理
这类子查询无法直接转成单层 LEFT JOIN，但可以拆解为 CTE 或派生表 + JOIN，避免重复计算。
例如：
SELECT o.* FROM Orders o WHERE o.UserId IN ( SELECT UserId FROM UserLogins GROUP BY UserId HAVING COUNT(*) > 5 );
优化写法：
WITH ActiveUsers AS ( SELECT UserId FROM UserLogins GROUP BY UserId HAVING COUNT(*) > 5 ) SELECT o.* FROM Orders o INNER JOIN ActiveUsers au ON o.UserId = au.UserId;
- CTE 不保证物化，但在 SQL Server 中加
OPTION (RECOMPILE)可促使优化器生成更优计划 - 若
UserLogins表极大，先在UserLogins(UserId)建索引，再考虑对COUNT(*) > 5做筛选前加WHERE LoginTime > DATEADD(day, -30, GETDATE())缩小输入范围 - 避免在 CTE 内使用
SELECT DISTINCT+JOIN，不如直接GROUP BY—— SQL Server 对聚合的统计信息利用率更高
LEFT JOIN + IN 混用时最容易忽略的坑
真正难优化的不是纯 LEFT JOIN，而是 LEFT JOIN 后又在 WHERE 里用 IN 过滤右表字段，比如：
SELECT o.*
FROM Orders o
LEFT JOIN Users u ON o.UserId = u.UserId
WHERE u.Status IN ('Active', 'Pending');
这段代码实际等价于 INNER JOIN，因为 u.Status 在 LEFT JOIN 中为 NULL 时无法满足 IN 条件。它既没利用 LEFT JOIN 的语义，又丧失了索引下推机会。
- 检查执行计划中是否出现
NULL-aware semi join或警告 “Predicate used in join condition was moved to WHERE clause” - 如果本意是“查所有订单，只显示状态为 Active/Pending 的用户信息”，应把
IN条件移到ON：ON o.UserId = u.UserId AND u.Status IN ('Active', 'Pending') - 如果本意是“只取有匹配且状态合规的订单”，直接改用
INNER JOIN，语义清晰且优化器更容易选索引
最常被忽略的一点：LEFT JOIN 的性能瓶颈往往不在连接本身，而在右表连接键上缺失索引，或连接后未及时用 WHERE 过滤驱动表——这时候加再多层优化也白搭。
