[hbrundage]

I agree and would take it one step further — the structure of the joins is something that should most often come from the schema, not the query. In the same way that the attributes of an entity should be modelled out ahead of time, the relationships between the entities should be as well, and that yields more productive querying and better performance.

We built a GraphQL / SQL hybrid expression language that does just this here: https://docs.gadget.dev/guides/data-access/gelly#gelly , curious what yall think.

[reaanb2]

I disagree. From a fact-oriented modeling perspective, relationships among entities are already well-modelled and represented in tables. There's a reason the relational model calls tables relations - they relate things. The mistake here is viewing tables/rows as representing entities, FK constraints as representing relationships, and viewing entities as containers of attributes rather than the subjects of facts.

Joining tables is composing complex facts from simple ones, and is the opposite of normalization which is decomposing complex facts into simpler ones. The ability to join tables on arbitrary conditions is fundamental to the ability to ask a DBMS complex questions and have it respond with all the facts that match that question.