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

Don't try to get nested results, fire off another query.

Although I would venture to guess you feel you need that 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 stupid things. SQL was specifically designed to be run on the same machine as your application.

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, 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 aren't the hacks that they are, but actually how SQL is designed to be used.

> I have not seen this in any other general interface for relational databases.

Oracle has their thing. Its name escapes me. SQL itself has json_agg/jsonb_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.

I can write a single join in SQL to get this, but then the customer information will be repeated on each purchase line. For my report I will also need to group orders from the same customer together. So now I have asked my database to duplicate the customer information across rows, only to deduplicate the data in my application again.

I can create two SQL queries, one for the customers, one for the purchases. I will still need to use joins (or WHERE-EXISTS clauses), because I do not want to extract all my customers and/or purchases from the database. Then, in the application I need to combine the results of those two queries. This will require me to implement the same join (or at least, its matching logic) yet again.

Neither of these really seem all that great to me. Of course, one can work around these limitations, many database adapters do. But in my opinion, this does not mean that SQL should not have provided a native solution for this. (There are tons of other things it could have left out as well, by that logic.)

The agg functions are probably the closest thing we have. I have mentioned my gripe with the JSON versions before, they lose valuable type information due to JSON's limited data model. The array_agg version is usually not very ergonomic on the receiving end. Both are also more cumbersome to write in SQL than I would like.

For what it's worth, I also think that SQL teaching generally emphasizes joins as the natural way to work with relational databases way to much. The agg functions might appear almost as an afterthought at the very end. (To be fair to the creators of teaching materials, to me, they also appear as an afterthought in SQL itself.)

Yes, this maps quite nicely to SQL. Consider this pseudocode:

    for customer in (select * from customers) {
        println(customer.name)
        for purchase in (select * from purchases where cid = customer.id) {
            println("\t"+purchase.date+": "+purchase.amount)
        }
        println()
    }

Join has a place. This is not it. Joins are for joins in the mathematical sense, not view aggregation.

> I also think that SQL teaching generally emphasizes joins as the natural way to work with relational databases way to much.

Yes, agreed, and I am suggesting that is because of the hacks required to work around the reasons why you cannot write the above code under shitty implementations, which has lead to many conflating joins and view aggregation.

If you want something like the above code except using a declarative language instead then you can throw something like GraphQL in front of it. What are you really gaining by turning SQL into GraphQL?

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.

It's a "new database" (actually a layer on top of Postgres). It has a more ergonomic query language, and it returns results as nested structures rather than rows. So "give me all my customers with a list of their orders" returns what you want.

Unfortunately, the startup that was developing Gel got acqui-hired, and Gel's future is unclear.

The second biggest flaw, which isn't really about SQL itself but in implementation, was attaching SQL to the network where latency starts to become a problem. That requires hacks to work around. This is likely this is why you're seeing data duplication — trading all that duplication to avoid the latency problems that wouldn't be a problem if you kept it on the same machine like SQL originally intended.