3 ms·
Easy: select school, (select array_agg(teacher) from teachers where teachers.school = schools.school) from schools; If you don't like nulls any more than o
by patrec 7y ago
Easy:
select school, (select array_agg(teacher) from teachers where teachers.school = schools.school) from schools;
If you don't like nulls any more than outer joins, you'd probably want:
select school, (select coalesce(array_agg(teacher), '{}'::text[]) from teachers where teachers.school = schools.school) from schools;
- derefr 7y agoYou realize that joins are a part of the formal definition of relational algebra, while subqueries aren't, right? SQL has had outer joins since the beginning, but subqueries came only gradually, and more limited SQL engines tend still to not support them.
- patrec 7y agoWhy would SQL (fundementaly) need outer joins? "Because it has them", is not really a compelling answer in this context. A "pure" relational model (like the one C.J. Date has been going on about for decades) has no outer join.