子查询在多对多关联中不能直接GROUP BY，因中间表重复行会导致主表数据“撑开”，使聚合失真；应改用EXISTS或标量子查询实现逻辑去重与精确计数。
子查询在多对多关联表中为什么不能直接 GROUP BY
因为多对多关系必然经过中间表（比如 user_role），一旦用 JOIN 连三张表再 GROUP BY user_id，就会因中间表重复行导致主表数据被“撑开”——例如一个用户有 3 个角色，users 行会被复制 3 次，COUNT(DISTINCT ...) 或 STRING_AGG 看似能补救，但聚合逻辑已偏离原始意图，且无法还原“该用户是否拥有某类角色”这类布尔判断。
用 EXISTS + 子查询替代 JOIN 实现逻辑去重
核心思路是：主表每行只查一次，子查询负责回答“是否存在满足条件的关联记录”，不引入重复行。适用于需要布尔标记、计数、或按主表维度聚合但不想被中间表数量干扰的场景。
-
EXISTS子查询里用WHERE关联主表字段（如u.id = ur.user_id），内部不SELECT *，只SELECT 1或任意常量 - 若需统计每个用户拥有的角色数，写成
(SELECT COUNT(*) FROM user_role ur WHERE ur.user_id = u.id)，比LEFT JOIN ... GROUP BY更直观且无歧义 - MySQL 8.0+ / PostgreSQL 支持标量子查询直接出现在
SELECT列表，但必须保证子查询返回 0 或 1 行；否则报错Subquery returns more than 1 row
用 LATERAL / APPLY 实现带参数的子查询分组（PostgreSQL / SQL Server）
当子查询需要基于主表每行动态计算多个值（比如每个用户的前 2 个角色名、最新登录时间），普通子查询受限于单值返回，这时 LATERAL（PostgreSQL）或 OUTER APPLY（SQL Server）能真正实现“每行驱动一次子查询”，且结果可参与后续 GROUP BY。
SELECT u.name, r.role_name FROM users u LATERAL ( SELECT role_name FROM roles ro JOIN user_role ur ON ro.id = ur.role_id WHERE ur.user_id = u.id ORDER BY ur.created_at DESC LIMIT 1 ) r;
注意：LATERAL 子查询可引用左侧表字段，但不可在 WHERE 中引用自身输出列；若要分组后取每个组的 Top-N，仍需配合窗口函数（如 ROW_NUMBER() OVER (PARTITION BY u.id ORDER BY ...)）。
容易忽略的性能陷阱：子查询未加索引或写成相关子查询
相关子查询（correlated subquery）每扫描主表一行就执行一次，若主表 10 万行、子查询没走索引，可能触发 10 万次全表扫描。关键优化点：
- 确保子查询中的关联字段（如
user_role.user_id）有索引，复合索引优先考虑(user_id, role_id)这类覆盖常用查询路径的顺序 - 避免在子查询
WHERE中对主表字段做函数操作，例如WHERE YEAR(u.created_at) = 2024会让EXISTS失去索引优势 - PostgreSQL 中可用
EXPLAIN ANALYZE观察子查询是否被转为Hash Semi Join（高效）还是SubPlan（逐行执行，慢）
真正难的不是写出子查询，而是判断它是否被数据库优化器“扁平化”——有时候看似简洁的写法，执行计划里却是最暴力的嵌套循环。
