alt Hacker NewsIt is the language's fault, because SQL offers no good way to get nested results. If you want to avoid the duplication, you can do multiple queries, but then you essentially have to do the join on the receiving end, which kind of defeats the purpose of having joins in SQL.
Alternatively, you might use functions to aggregate in the query, but they're not standard, as far I know, which means that interfaces in programming languages often do not support them. The best bet might be aggregating into JSON, but this removes quite a bit of the type information that values in SQL usually carry. (Also, the aggregation functions are generally less pleasant to write than standard joins.)
Replies
Yeah, ARRAY_AGG being an optional part of the standard is annoying, and in general the array handling functions have been very non-standardized and messy - it's getting better though!
> because SQL offers no good way to get nested results
Because you don't need nested results. You can fire off another query.
Although I am venturing to guess that you feel you need nested queries because you're doing doing something stupid like trying to send SQL queries over the network where the cost of firing off another query becomes a great performance burden. But the answer to that is don't do that. SQL was specifically designed to be run on the same machine as your application.
If you need to present the data to network clients where nesting is necessary for performance reasons, turn your application into the network database service that produces nested data (the "API server" as the kids call it). Leave the SQL as an implementation detail hidden behind the scenes.