有几种方法可以做到这一点。下面的查询首先确定所有唯一的学生和地址对。查询的主要部分使用这些来连接学生和地址。

WITH
  visited_addresses AS (
    SELECT student_id, address_id
      FROM School
    UNION
    SELECT student_id, address_id
      FROM College
  )
SELECT s.id AS "ID",
       s.name AS "Student Name",
       string_agg(a.name, ', ' ORDER BY a.name) AS "Visited Places"
  FROM Student s
  LEFT JOIN visited_addresses v
    ON s.id = v.student_id
  LEFT JOIN Address a
    ON v.address_id = a.id
 GROUP BY s.id, s.name
 ORDER BY s.id;

下一个查询返回相同的结果，但使用横向连接来获取每个学生的访问地址：

SELECT s.id AS "ID",
       s.name AS "Student Name",
       string_agg(a.name, ', ' ORDER BY a.name)
  FROM Student s
  LEFT JOIN LATERAL (SELECT sc.address_id
                        FROM School sc
                       WHERE sc.student_id = s.id
                      UNION
                      SELECT c.address_id
                        FROM College c
                       WHERE c.student_id = s.id
                     ) v
    ON TRUE
  LEFT JOIN Address a
    ON v.address_id = a.id
 GROUP BY s.id, s.name
 ORDER BY s.id;