应按语义拆解where中多个子查询：集合判断用in，存在性检查优先exists（可提前终止、避null陷阱），单值比较须确保1行1列；复杂逻辑用with分步计算，避免深层嵌套与重复执行。
WHERE里嵌套多个子查询不是不行，但直接堆砌容易出错、难维护、性能差——真正该做的是按语义拆解：哪些是集合判断，哪些是存在性检查，哪些必须单值比较。
用 IN 处理“属于某集合”的多条件
比如要查“既买过品类A、又买过品类B、还下单过VIP活动商品”的用户，别写三个独立子查询用 AND 连接。每个子查询都得全表扫一遍，执行三次。
- 正确做法是把每个条件转成独立的
IN子句，但注意它们之间是交集关系，不是并列条件 - 更稳的写法是用多个
EXISTS，因为EXISTS能利用索引提前终止，且天然规避NULL导致整条条件失效的问题 - 如果子查询结果固定且小（比如几十个预设商品ID），可以先用
WITH提前算好，再在 WHERE 中引用，避免重复执行
用 EXISTS 替代多个相关子查询
常见错误是这样写：WHERE user_id IN (SELECT user_id FROM orders WHERE status = 'paid') AND user_id IN (SELECT user_id FROM logs WHERE action = 'login')。这看起来清晰，但 MySQL 会分别执行两个子查询，且无法利用外层 user_id 索引做下推优化。
-
EXISTS是关联执行的，数据库能将外层字段作为参数传入内层，配合索引快速定位 - 多个
EXISTS用AND连接，语义明确是“同时满足”，不会因某子查询返回空而让整行消失（IN遇到空集会返回空结果） - 务必在子查询的关联字段上建索引，否则
EXISTS也会退化成全表扫描
标量子查询只允许 1 行 1 列，别让它“超载”
当你在 WHERE 中写 amount > (SELECT AVG(amount) FROM orders WHERE user_id = u.id)，这是合法的——它返回单值。但一旦改成 user_id = (SELECT id FROM users WHERE tag IN ('vip', 'trial'))，就立刻报错 Subquery returns more than 1 row。
- 标量比较符（
=、>、BETWEEN等）后面只能跟一个值，MySQL 不会自动取第一个或报警告，而是直接中断 - 想强制取一行，可用
LIMIT 1（MySQL）或FETCH FIRST 1 ROW ONLY（标准 SQL），但业务逻辑是否允许“随便挑一个”必须确认清楚 - 更安全的做法是换用
IN或重构为JOIN，尤其当子查询本意就是匹配多个可能值时
深层嵌套时优先用 WITH 拆解逻辑
三层以上子查询不仅人看不懂，MySQL 5.7 及更早版本会拒绝优化，PostgreSQL 的执行计划也常失真。真实场景中，“高价值用户”可能定义为：近30天订单数 ≥ 3 且 平均客单价 > 500 且 有退款失败记录 且 标签包含 ‘active’ —— 这种组合别硬塞进一个 WHERE。
- 用
WITH把每个维度拆成临时结果集，比如recent_orders、high_value_users、failed_refunds - 最后用主查询
JOIN这些 CTE，语义清晰、可读性强，数据库也更容易生成合理执行计划 - CTE 不是视图，不物化数据；但现代引擎（MySQL 8.0+、PostgreSQL）会对多次引用的 CTE 自动缓存结果，比重复子查询更高效
最容易被忽略的一点：相关子查询（即引用了外层字段的子查询）在 WHERE 中看似简洁，但极易导致索引失效——特别是当外层字段没走索引，或子查询里用了函数、类型隐式转换时。动手前先跑 EXPLAIN，看是不是真的走了索引。
