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.