If anything a well designed database minimises data duplication. If your query returns duplicate data that's on the writer, not the language's fault.
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.)
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.
As I mentioned in the previous post, I am aware that this can be done with multiple queries and post-processing, but then I am implementing what essentially amounts to a join myself (of maybe I have something like an ORM do it for me). In any case, my data processing is now split up in two places.
And the point of Ultorg is that it provides an interface that does this post-processing into a nested representation. Except maybe for Gel (mentioned somewhere else here), I have not seen this in any other general interface for relational databases. If SQL supported nested results, this surely would be different.
It is a higher level concern so that isn't a bad thing. SQL is focused on giving you a view of your data. Whereas you want to combine many different views. That is where composing many different queries should be seen as quite natural at the interface point, not a shortcoming of SQL. If that composition isn't natural, you may want to take a closer look at your application's architecture.
> but then I am implementing what essentially amounts to a join myself
Joins and aggregated views are very different concepts. I think this misunderstanding, which is one I've seen many times before, stems from the hacks used to work around the aforementioned problems. Many have come to believe those hacks actually how SQL is designed to be used, not the hacks that they are.
> I have not seen this in any other general interface for relational databases.
Oracle has their thing. Its name escapes me at the moment. Postgres has json_agg/jsonb_agg and similarly SQL itself has array_agg. Lots of options out there.
But it's not only a question of if it can be done, but if it should be done. There are a lot of other advantages to moving query aggregation out into another application. There is a reason why we stopped sticking SQL servers directly in front of the clients and it isn't just because of SQL using a table representation.
Half the reason why ORMs have trouble has to do with the fact that you need to fetch has many relations via a second select.
Beyond that, it's the GPPL languages' fault that it doesn't support object or class fragments which are needed to support queries that return a subset of an object.
If people bothered fixing these two issues, then ORMs would be great.