子查询能替代部分JOIN场景：当主表小、关联表大且只需存在性判断或单值聚合时，EXISTS/IN更高效；但取多字段或分组聚合时JOIN仍首选。

子查询能替代哪些 JOIN 场景？ 当主表数据量小、关联表数据量大，且只需要判断存在性或单值聚合时，EXISTS 或 IN 子查询往往比 JOIN 更快。比如查“有订单的用户”，用 EXISTS 可避免重复行和临时表排序；而用 JOIN 再 DISTINCT 就多了一步去重开销。

• EXISTS 适合“是否存在”逻辑，数据库通常能短路执行（找到第一条就停）
• IN (SELECT ...) 要小心 NULL：如果子查询返回任何 NULL，整个条件判为 UNKNOWN，结果为空——这常导致查不到数据却无报错
• JOIN 在需要取关联表多个字段、或要做分组聚合时仍是首选，子查询硬套反而让语义变模糊

WHERE 中嵌套子查询 vs. FROM 中派生表，怎么选？ 关键看是否复用。如果子查询结果要被多次引用（比如同时用于筛选和计算），必须提升到 FROM 子句作为派生表（或 CTE），否则重复执行会拖慢性能。

• WHERE 里的标量子查询（返回单值）会被每行执行一次，若子查询含聚合或扫描大表，代价极高
• FROM (SELECT ...) AS t 派生表只执行一次，后续可加索引提示、甚至物化（取决于数据库，如 PostgreSQL 的 MATERIALIZED CTE）
• MySQL 5.7+ 对派生表默认自动内联优化，但遇到 GROUP BY 或 LIMIT 可能禁用优化，需加 /<em>+ DERIVED_CONDITION_PUSHDOWN </em>/ 提示（MySQL 8.0.23+）

相关子查询为什么慢？如何改写？ 相关子查询（即子查询里引用了外层表字段）本质是“循环执行”，比如：SELECT u.name, (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) FROM users u。10 万用户 = 执行 10 万次子查询。

• 改写优先用 LEFT JOIN + GROUP BY：把子查询逻辑前置聚合，再关联主表
• 若无法 JOIN（如子查询含复杂窗口函数），可先用 CTE 预算好聚合结果，再 JOIN，避免重复计算
• PostgreSQL 和 SQL Server 支持 LATERAL（或 APPLY），能更自然地表达“每行驱动一次子查询”，且支持优化器下推，比传统相关子查询更可控

子查询结果为空时，NOT IN 为何不返回预期数据？ 这是最隐蔽也最常踩的坑。NOT IN (SELECT status FROM logs WHERE user_id = 123) —— 如果子查询结果包含 NULL，整个表达式恒为 FALSE，哪怕外层值根本不在结果集中。

• 直接等价写法是：NOT EXISTS (SELECT 1 FROM logs WHERE user_id = 123 AND status = outer.status)
• 或强制过滤 NULL：status NOT IN (SELECT status FROM logs WHERE user_id = 123 AND status IS NOT NULL)
• 更稳妥的做法是统一用 NOT EXISTS 替代 NOT IN，尤其在子查询来源不可控时（比如视图、用户输入拼接）

子查询不是银弹，它让逻辑更贴近自然语言，但也更容易掩盖执行计划膨胀。真正影响性能的，往往是子查询是否触发重复扫描、是否阻断索引下推、以及优化器能否识别语义等价性——这些没法靠语法改写解决，得看 EXPLAIN 输出里的实际行数和类型。