子查询在一对多场景易致重复或错误结果，应优先用exists替代in；取最新记录宜用窗口函数或lateral/apply；left join聚合需防null陷阱；旧版mysql可用派生表优化子查询性能。
子查询在一对多场景下容易返回重复或错误结果
直接用 WHERE ... IN (SELECT ...) 处理一对多关联，常导致主表记录被意外过滤或重复——因为子查询只负责“判断存在性”，不控制关联粒度。比如查“有订单的用户”，IN 没问题；但查“每个用户的最新一笔订单金额”，用 IN 就完全跑偏。
- 优先考虑
EXISTS替代IN：语义更清晰（“是否存在匹配行”），且通常能走索引，避免子查询全量展开 - 若需取一对多中的某条（如最新、最高），别在
WHERE子句里套子查询，改用JOIN + 窗口函数或LATERAL（PostgreSQL）/APPLY（SQL Server） - MySQL 5.7 及更早版本对
IN子查询有物化限制，遇到大结果集可能临时表溢出，报错ERROR 1205 (HY000): Deadlock found或慢得离谱
用窗口函数替代相关子查询提升性能
传统写法如 SELECT u.name, (SELECT amount FROM orders o WHERE o.user_id = u.id ORDER BY created_at DESC LIMIT 1) AS last_amount FROM users u，每行主表都触发一次子查询，N×M 复杂度。换成窗口函数，一次扫描搞定。
- 通用写法：
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC)，再外层WHERE rn = 1 - PostgreSQL / SQL Server / MySQL 8.0+ 支持，但 SQLite 不支持窗口函数，得退回到
JOIN+ 聚合或应用层处理 - 注意
PARTITION BY字段必须和主表关联字段一致，否则分组错位；ORDER BY里最好包含唯一字段（如id），避免并列排序导致rn非确定
LEFT JOIN 配合聚合时 NULL 值陷阱
一对多用 LEFT JOIN 后接 GROUP BY，看似自然，但容易忽略聚合函数对 NULL 的处理逻辑——比如 MAX(order_amount) 会跳过 NULL，而 COUNT(*) 会把空关联也计为 1。
- 查“用户是否有订单”别用
COUNT(*) > 0，改用COUNT(o.id) > 0，避免没订单时o.id为NULL导致COUNT(*)返回 1 -
STRING_AGG(o.item_name, ', ')在无订单时返回NULL，不是空字符串，业务代码需判空 - PostgreSQL 中
array_agg(o.item_name)同样返回NULL而非空数组，要写成COALESCE(array_agg(o.item_name), ARRAY[]::TEXT[])
MySQL 8.0 之前无法下推子查询条件？用派生表绕过
旧版 MySQL 对 WHERE 中子查询的优化能力弱，尤其嵌套层级深时，常出现全表扫描。一个有效缓解方式是把子查询提前物化为派生表（derived table），让优化器有机会用上索引。
- 坏写法：
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE status = 'paid') - 好写法：
SELECT u.* FROM users u INNER JOIN (SELECT DISTINCT user_id FROM orders WHERE status = 'paid') o ON u.id = o.user_id - 注意加
DISTINCT或GROUP BY，否则派生表可能含重复user_id，引发主表记录膨胀 - 该技巧在 MariaDB 10.2+ 和 MySQL 5.7 中实测可降低 60%+ 执行时间，但会额外消耗临时内存，超大结果集慎用
一对多不是语法问题，是建模和执行计划的问题。子查询写得再“对”，如果没对齐数据语义（比如该用 EXISTS 却用了 IN），或者忽略了目标数据库对窗口函数、派生表的实际支持程度，结果就只是看起来像对的。
