SQL中如何按university_id关联合并两个查询结果集为单个结果集

核心问题 #

UNION/UNION ALL的作用是纵向拼接结果集——要求两个查询的返回列数、对应列的数据类型完全匹配，把第二个查询的行追加到第一个查询的结果末尾。
你两个查询的返回列数根本不匹配：第一个查询返回5个字段，第二个查询只返回2个字段，强行拼接要么触发列数不匹配报错，要么列位错位，完全达不到你要的横向拼列效果。

正确实现方式 #

你需要通过共同字段university_id做横向关联拼接，用JOIN即可实现，不需要拆分两次查询手动合并。
可直接运行的SQL代码：

SELECT 
  u.university_id,
  u.name,
  u.short_name,
  u.region_id,
  r.name AS region,
  COALESCE(e.stu_count, 0) AS count
FROM universities u
INNER JOIN regions r 
  ON u.region_id = r.region_id
LEFT JOIN (
  SELECT 
    university_id, 
    COUNT(student_id) AS stu_count
  FROM enrollments
  GROUP BY university_id
) e 
  ON u.university_id = e.university_id;

注意点 #

• 这里用LEFT JOIN关联学生数的聚合结果，是为了避免没有任何学生记录的学校被直接过滤；如果业务上确定所有学校都有对应学生，换成INNER JOIN也可以，输出结果和你预期的格式完全一致
• 用COALESCE把空值转成0，是为了避免没学生的学校对应的count字段显示空值
• 原查询里写的WHERE u.region_id = r.region_id是内连接的冗余条件，直接把关联规则写在JOIN的ON子句里逻辑等价，代码更简洁
• 如果要追求更高性能，也可以给enrollments表的university_id字段建索引，避免聚合时全表扫描

内容的提问来源于stack exchange，提问作者Siri