一对多JOIN后用WHERE过滤会误删主表记录，因WHERE作用于连接结果集；应先用子查询/CTE预筛从表ID再关联，聚合需在预汇总层完成以避免重复计数。
为什么直接在 JOIN 后用 WHERE 过滤一对多关系会出错
因为一对多关联时，主表一行可能对应从表多行，WHERE 条件作用于连接后的结果集，容易把本该保留的主表记录“连带过滤掉”。比如查“订单+订单项”，想只取含「iPhone」商品的订单，但若写 WHERE item.name = 'iPhone'，会导致一个订单里有 iPhone 和 iPad 两条记录时，iPad 那行被过滤，iPhone 那行留下——主表订单仍出现，看似正确；但如果后续加了 GROUP BY order.id 或聚合统计，就可能漏掉本应参与汇总的其他订单项。
用子查询或 CTE 在 JOIN 前筛出符合条件的从表 ID 集合
核心思路：先明确哪些从表记录满足条件，再提取其外键（如 order_id），最后用这个集合去驱动主表关联。这样主表只和“相关”的从表子集连接，避免膨胀和误过滤。
- 推荐用
IN+ 子查询（简单场景）：SELECT o.* FROM orders o WHERE o.id IN (SELECT DISTINCT order_id FROM order_items WHERE name = 'iPhone');
- 更清晰可复用时，改用 CTE：
WITH target_orders AS (SELECT DISTINCT order_id FROM order_items WHERE name = 'iPhone') SELECT o.* FROM orders o INNER JOIN target_orders t ON o.id = t.order_id;
- 注意
DISTINCT不可省——否则子查询可能返回重复order_id，虽不影响结果，但降低效率
需要聚合统计时，必须在预汇总层完成计算
如果目标不只是筛选，还要算每个订单的总金额、商品数等，就不能只取 ID，得提前把聚合结果算好，再 JOIN 主表。否则在主表 JOIN 后再 GROUP BY，仍会因一对多导致重复计数。
- 错误写法（重复累加）：
SELECT o.id, SUM(i.amount) FROM orders o JOIN order_items i ON o.id = i.order_id GROUP BY o.id;
—— 若订单有 3 条项，SUM 就算 3 次 - 正确做法：先聚合从表，再关联
WITH item_summary AS (SELECT order_id, SUM(amount) AS total_amount, COUNT(*) AS item_count FROM order_items GROUP BY order_id) SELECT o.*, s.total_amount, s.item_count FROM orders o JOIN item_summary s ON o.id = s.order_id;
- 若还需过滤（如只看总金额 > 1000 的订单），把
WHERE放在 CTE 内部，而非最终 SELECT 外层
MySQL 8.0+ 和 PostgreSQL 中用 LATERAL / JOIN LATERAL 简化逻辑
当预汇总依赖主表字段（比如按用户地区动态取最近 3 笔订单），传统子查询难表达。这时 LATERAL 允许从表子查询引用主表列，且天然支持“每行主表 → 独立执行一次子查询”，语义更贴近“预汇总”。
- PostgreSQL 示例：
SELECT u.name, last_orders.total FROM users u JOIN LATERAL (SELECT SUM(amount) AS total FROM orders WHERE user_id = u.id ORDER BY created_at DESC LIMIT 3) last_orders ON true;
- MySQL 8.0+ 用
JOIN ... ON (1=1)模拟，但需确保子查询带WHERE引用主表列，优化器才能识别为 LATERAL 行为 - 注意：LATERAL 子查询必须返回 0 或 1 行（若允许多行，要用
JOIN LATERAL而非LEFT JOIN LATERAL），否则结果行数不可控
实际写的时候，最容易忽略的是预汇总是否覆盖了全部业务条件——比如过滤条件含时间范围，但子查询没加 created_at BETWEEN ...，或者聚合时忘了 NULL 值处理（SUM() 忽略 NULL，但 COUNT(*) 不忽略）。这些细节不检查，结果就 quietly 错了。
