子查询应根据复用需求选择位置：需复用中间结果时用FROM（派生表），仅单次判断用WHERE；相关子查询性能差，优先改写为JOIN；EXISTS比IN更稳定但无法取值；CTE非自动物化，慎用。
子查询写在 WHERE 里还是 FROM 里？看你要不要复用中间结果
WHERE 中的子查询（标量子查询或 IN/EXISTS）每次外层行都会执行一次，性能容易崩；FROM 中的子查询（派生表）只执行一次，结果可被多次引用，适合逻辑分层。业务建模时，如果某段计算逻辑（比如“每个部门最新一笔订单时间”）要在多处用到，硬塞进 WHERE 会导致重复执行和难以维护。
-
WHERE子查询适合单次判断：比如WHERE user_id IN (SELECT id FROM banned_users)，但要注意子查询返回NULL时整个条件变UNKNOWN，可能漏数据 -
FROM子查询必须加别名：比如SELECT * FROM (SELECT dept_id, MAX(order_time) AS last_time FROM orders GROUP BY dept_id) AS dept_last，漏掉AS dept_last在 MySQL 8.0+ 会报错Every derived table must have its own alias - PostgreSQL 和 SQL Server 支持 CTE（
WITH），语义更清晰、支持递归，但 MySQL 5.7 不支持，升级前得先确认版本
EXISTS 比 IN 更稳，但别无脑套用
EXISTS 只关心子查询是否返回行，不取值，天然规避 IN 遇到 NULL 就失效的问题；但它不能直接拿到子查询里的字段值——想同时查出“用户姓名”和“他是否有未读消息”，EXISTS 就得配合外部 JOIN，而 IN 或关联子查询反而更直白。
-
IN子查询若右边含NULL（如SELECT * FROM users WHERE id IN (SELECT user_id FROM logs WHERE status IS NULL)），整条IN判断结果为UNKNOWN，该行被过滤掉——这常是线上漏数据的隐性原因 -
EXISTS对索引友好，尤其当子查询带WHERE条件时，数据库更容易用上user_id索引；但若子查询本身没过滤条件（如EXISTS (SELECT 1 FROM logs)），它会全表扫logs表一次，比预期还慢 - MySQL 5.6+ 对
IN的常量列表做了优化，但若右边是子查询，仍可能退化成 N+1 查询；用EXISTS能避免，前提是子查询里有合理驱动条件（如WHERE logs.user_id = users.id）
相关子查询为什么慢？因为它的执行次数由外层决定
相关子查询（correlated subquery）里引用了外层表字段，比如 (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id)，它不是一次性算完，而是对 users 表每行都执行一遍。10 万用户，就执行 10 万次子查询——哪怕子查询只走索引，I/O 和解析开销也扛不住。
- 能转成 JOIN 就别用相关子查询：上面例子改写为
LEFT JOIN (SELECT user_id, COUNT(*) AS cnt FROM orders GROUP BY user_id) o ON o.user_id = u.id，执行一次聚合 + 一次哈希连接，快一个数量级 - 某些场景无法避免相关子查询，比如“找每个用户第二笔订单”——这时务必确保子查询里有覆盖索引，例如
(SELECT order_id FROM orders WHERE user_id = u.id ORDER BY create_time LIMIT 1 OFFSET 1)需要联合索引(user_id, create_time)，否则就是全表扫描 - Oracle 和 PostgreSQL 支持
LATERAL（MySQL 8.0.14+ 实验性支持），可让子查询“感知”外层变化又保持结构清晰，但生产环境慎用，兼容性和执行计划不稳定
CTE 不是银弹：WITH 块可能被多次执行
很多人以为 WITH 是缓存中间结果，其实标准 SQL 中 CTE 只是语法糖，数据库可以展开、重写甚至重复执行。PostgreSQL 默认会展开，除非加 MATERIALIZED（12+ 版本）；SQL Server 用 OPTION (RECOMPILE) 才可能物化；MySQL 8.0 目前不支持物化，WITH 完全等价于子查询嵌套。
- 别在 CTE 里写耗时操作然后反复引用，比如
WITH big_calc AS (SELECT ..., SLEEP(1) FROM huge_table)，后面每引用一次就睡一秒 - MySQL 8.0 中，CTE 不能被索引，也不能被
UPDATE/DELETE直接修改；想更新中间结果，只能用临时表：CREATE TEMPORARY TABLE tmp_result AS SELECT ... - 嵌套太深的 CTE（超过 3 层）会让执行计划难以调试，EXPLAIN 看到的往往是展开后的巨长语句，不如拆成带注释的临时表步骤来得实在
事情说清了就结束。逻辑分层不是靠多套几层子查询，而是看哪部分计算稳定、可复用、可测试——该提成临时表就提，该加索引就加，别迷信语法糖。
