[y4mi]

honest question, when would you query several tables without joining them somewhere?

you can do that on postgres as well btw, my first idea would be to create a function [1] and just throw the rows together. still can't imagine a usecase for that though, i'd always want to get them separately if they're unrelated.

you're correct on the GUI side however. Everything i've tried is a buggy mess. i've actually mostly given up on that front and just went with pgcli [2]. its pretty good for ad hoc queries. But it doesn't really help if you're trying to write a function. Its just nice for selects with lots of joins or nested selects.

[1] https://www.postgresql.org/docs/9.1/static/sql-createfunctio... [2] https://github.com/dbcli/pgcli

[hungerstrike]

The databases I build exist only to serve the application and I prefer to tightly couple the data access to the database itself, thereby addressing the object/relational impedance mismatch in one simple stroke.

I usually write queries that return multiple resultsets when the caller needs to get a bunch of data from different tables all at once. (So, all of the time.) Instead of sending each table query to the database server individually, I just query one "stored procedure" in SQL Server. It saves a lot of round-trips on the network and there are other benefits too.

For instance, say my API needs to take the relational data from tables `Customer, Order, OrderItem(s) and OrderShipment(s)` and return them as a JSON object `{order:{customer: {...}, items: [...], shipments: [...]}}`. Or, say I need to render a PDF using those same tables. In either case the client needs to get a whole graph of data out of the database and with SQL Server, they can only send one little query to call my procedure: `EXEC dbo.GetCustomerWithOrderDetail @OrderId=999;` instead of sending 4 or 5 single-resultset queries.

Sometimes I do also end up sending 2 or 3 procedure queries separately, for instance the client might send a query for `GetCustomerWithOrderDetail` followed by `GetPdfTemplateWithDetailForRendering`. So, I get code encapsulation too.

The benefits of this style are exponential in my opinion. Most of the time, my client apps don't even need to know the names of the tables because they are calling procedures, not embedding "SELECT * FROM TABLE" in the client code. The only minor drawback is that the client does need to know the order that the resultsets are returned in ahead of time.

I think there are a few ways to attempt this in PG but none of them seem to come very highly recommended. I looked around just now and found this post:

https://stackoverflow.com/questions/36717138/postgres-functi...

You can see the programmer wants to do what they need to in a very simple way, similar to how it would be done in SQL Server. However, it's not possible. Here is an article that explores some of the options in PG and the problems with each approach:

https://blog.dsl-platform.com/multiple-result-sets-alternati...

[foodie_]

Interesting. I worked on a larger project that used Oracle and a similar approach. The maintainers of PG actually advocate for this abstraction as well (maybe not the multiple responses, not familiar with that aspect) but most developers and frameworks ignore it.

[flukus]

> honest question, when would you query several tables without joining them somewhere?

You might be "joining" client side if you had something like a master/detail UI. Network round trips are relatively slow and to be avoided if you can (n+1 errors being the pathological case):

https://people.eecs.berkeley.edu/~rcs/research/interactive_l...