3 ms·
Show me all schools in the system, and their associated teachers, including schools which have no teachers.
by pavel_lishin 7y ago
Show me all schools in the system, and their associated teachers, including schools which have no teachers.
- patrec 7y agoEasy: 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.