Re:Ultorg: The User Interface for Relational Databases
by win311fwg on Wednesday November 11, 2026 @04:00AM
(#50451151)
> To be explicit, I'll use "Give me a list of all my customers in Oregon who have made a purchase within the last year together with those purchases" as an example.
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?
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.)