Handling Null Values in Postgres

Re:Handling Null Values in Postgres by pavel_lishin on Thursday March 05, 2020 @04:56PM (#22498778)

Show me all schools in the system, and their associated teachers, including schools which have no teachers.

[ Parent | on HN ]

Re:Handling Null Values in Postgres by patrec on Thursday March 05, 2020 @06:22PM (#22499482)

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;

[ Parent | on HN ]

Re:Handling Null Values in Postgres by derefr on Thursday March 05, 2020 @09:11PM (#22500449)

You 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.

[ Parent | on HN ]

Re:Handling Null Values in Postgres by patrec on Friday March 06, 2020 @03:06AM (#22501799)

Why 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.

[ Parent | on HN ]