WHERE子句中嵌套SELECT只生成值列表或布尔结果，真正过滤外层数据的是外层WHERE对主表每行的逐行判断；类型不匹配、NULL值、多列返回等均会导致异常。

WHERE子句里嵌套SELECT，到底过滤哪一层的数据？

嵌套查询中WHERE里的子查询（比如WHERE id IN (SELECT user_id FROM logs)）只负责生成一个值列表或布尔结果，它本身不参与外层的行过滤逻辑——真正决定“哪几行留下”的，是外层WHERE条件对主表每一行的逐行判断。

常见错误现象：SELECT * FROM orders WHERE status = 'shipped' AND user_id IN (SELECT id FROM users WHERE is_vip = 1) 返回空结果，但你查了users表确认有is_vip = 1的用户。问题往往出在user_id和users.id类型不一致（比如一个是INT、一个是VARCHAR），导致隐式转换失败，整个IN判为FALSE。

• 子查询必须返回单列；多列会报错：ERROR: subquery must return only one column
• IN对NULL敏感：如果子查询结果含NULL，整条IN表达式可能无法匹配任何行（SQL标准行为）
• 性能上，MySQL 5.7+ 对简单IN (SELECT ...)会自动转成SEMI JOIN，但PostgreSQL默认不优化，可能全表扫描外层表

LEFT JOIN + WHERE vs LEFT JOIN + ON：筛选时机差一毫秒

连接后加WHERE会把LEFT JOIN变成事实上的INNER JOIN——因为WHERE是在连接完成后再过滤，而ON是在连接过程中决定哪些右表行能被拉进来。

使用场景：你想列出所有用户，同时显示他们最近一笔订单的金额，但没下单的用户金额应为NULL。如果写成LEFT JOIN orders ON users.id = orders.user_id WHERE orders.status = 'paid'，那些没付款订单的用户就直接消失了。

• 想保留左表全部行 → 筛选条件必须写进ON，如：LEFT JOIN orders ON users.id = orders.user_id AND orders.status = 'paid'
• 想只取有匹配且满足条件的行 → 用INNER JOIN更直白，别硬套LEFT JOIN + WHERE
• MySQL 8.0+ 支持LATERAL，可让右表子查询依赖左表字段，比复杂ON更可控

EXISTS比IN更安全？看这三点再决定

EXISTS和IN语义不同：IN关注“值是否在集合中”，EXISTS只关心“子查询是否返回至少一行”。当子查询可能返回NULL，或外层字段本身为NULL时，EXISTS更稳定。

典型坑：SELECT * FROM products WHERE category_id IN (SELECT id FROM categories WHERE active = 1)，如果categories.id允许NULL，且子查询真返回了NULL，那么整个IN表达式对任意category_id都判为UNKNOWN，该行被过滤掉。

• EXISTS不受NULL影响，只要子查询有行就返回TRUE
• 多数引擎对EXISTS能提前终止（找到第一行就停），IN常需收全结果集
• 但EXISTS不能用索引覆盖优化，若子查询要关联大表且无合适索引，反而更慢

嵌套查询改写成JOIN时，GROUP BY和聚合怎么处理？

带GROUP BY或聚合函数的子查询（比如(SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id)）不能简单替换成JOIN，否则会因笛卡尔积放大行数，导致聚合结果翻倍。

正确做法是先聚合再连接，或用LATERAL/APPLY（SQL Server）、JOIN LATERAL（PostgreSQL）保证每行只触发一次子查询。

• MySQL 8.0+ 可用JOIN (SELECT ... GROUP BY) AS agg ON ...，但要注意GROUP BY字段必须和ON条件对齐
• 避免在SELECT里写相关子查询做聚合——执行效率极低，尤其在外层表大时
• 如果只是统计存在性（如“是否有订单”），用EXISTS比(SELECT COUNT(*) ...) > 0快得多，也更易读

最麻烦的其实是子查询里用了ORDER BY + LIMIT又想连到主表——这时候几乎只能靠LATERAL或窗口函数，传统JOIN根本没法表达“每个用户的最新一条订单”这种逻辑。