子查询应按语义分层：标量子查询用于 WHERE/SELECT 中单值比较，派生表用于 FROM 中避免 N +1，CTE 用于多步依赖计算；错误嵌套会导致性能崩溃或语法报错。
子查询替代多层 JOIN 做分步聚合
当需要先按某维度汇总、再基于汇总结果做二次计算（比如“每个部门平均薪资高于公司平均的部门”），硬写成单条 JOIN 语句容易逻辑缠绕、可读性差，还可能因 JOIN 顺序引发重复计数。用子查询把“算公司均值”和“比对部门均值”拆开，SQL 更直白，执行计划也更容易被优化器识别。
常见错误现象：GROUP BY 后直接 HAVING AVG(salary) > AVG(salary) —— 这会报错或返回空，因为无法在同一层级混用聚合上下文。
- 第一步：用子查询算出全局基准值，比如
(SELECT AVG(salary) FROM employee) - 第二步：主查询中用该子查询结果作比较条件，例如
WHERE dept_avg > (SELECT AVG(salary) FROM employee) - 注意子查询必须是标量子查询（返回单值），否则会触发
Subquery returns more than 1 row错误
WHERE 中的 EXISTS 比 IN 更稳地处理透视过滤
想查“有销售记录且最近 30 天有退货的客户”，用 IN 常会因 NULL 值或数据量大变慢；EXISTS 子查询天然规避 NULL 陷阱，且多数引擎能提前终止扫描。
使用场景：主表大、关联条件带时间范围或状态筛选时，EXISTS 通常比 IN 或 JOIN 更轻量。
-
IN要求子查询先执行完并去重，内存压力大；EXISTS是对外表每行做一次存在性判断，可短路 - 示例：查有活跃订单的客户
WHERE EXISTS (SELECT 1 FROM order o WHERE o.customer_id = c.id AND o.status = 'active') - 别忘了在子查询里加
SELECT 1而不是SELECT *，避免无谓字段传输
CTE 不只是语法糖，它是控制计算依赖的关键
复杂透视常需多个中间结果（如：各渠道转化率 → 渠道加权平均 → 渠道排名），用 CTE 把每步命名，既避免重复写子查询，又能明确计算顺序——尤其当某步结果被后续多处引用时，CTE 让执行计划更可控。
性能影响：PostgreSQL 和 SQL Server 会物化 CTE（除非加 NOT MATERIALIZED），但 MySQL 8.0+ 默认不物化，反复引用同一 CTE 可能多次执行底层查询。
- 写法上，CTE 必须紧跟
WITH，不能在CREATE VIEW或存储过程中直接嵌套多层WITH - 别把 CTE 当变量用：CTE 内不能引用自身（递归除外），也不能跨 CTE 引用未定义的别名
- 简单示例：
WITH dept_stats AS (SELECT dept, AVG(salary) avg_sal FROM emp GROUP BY dept)，后续可直接SELECT * FROM dept_stats WHERE avg_sal > 10000
子查询位置决定执行时机和结果稳定性
子查询写在 SELECT 列表、WHERE、FROM 或 HAVING，语义和执行行为完全不同。最容易踩的坑是把本该在 FROM 的派生表，错写进 SELECT——导致每行都执行一次子查询，性能断崖式下跌。
典型错误：SELECT id, name, (SELECT COUNT(*) FROM log l WHERE l.user_id = u.id) cnt FROM user u —— 表面看是“每人日志数”，实际变成 N+1 查询，大数据量直接卡死。
- 要聚合统计，优先走
FROM子句的派生表或 CTE，让数据库一次性算完再 JOIN -
SELECT中的子查询只能是标量子查询，且必须确保返回 0 或 1 行，否则报错Subquery returns more than 1 row - MySQL 5.7 对
FROM子查询要求别名，漏写会报Every derived table must have its own alias
事情说清了就结束。子查询不是越嵌套越高级，关键是让每一步计算意图清晰、可验证、不被优化器意外改写。
