I love the idea of a simplified SQL syntax for joins.<p>I have been working with graph databases for years now: these databases had to solve this problem from day one, because of the focus on relationships between entities.<p>I must point out that Neo4j was the first to propose a syntax that made traversal feel simple and natural again: the Cypher query language.<p>Neo4 and other industry players have spent years working on a new standard query language for graph databases that was released in April this year: GQL. GQL is the first database query language normalized by ISO since SQL, so it’s a big deal.<p>Anyway, if you wanna learn more about GQL, that a look at
<a href="https://www.gqlstandards.org/" rel="nofollow">https://www.gqlstandards.org/</a>
The presented idea shortens the given examples, but is not composable. What happens if you have 1:N instead of 1:1 relation? Or even a N:M relation. Where do you specify whether you want an innner / outer / left / right join?
This proposal works for some simple queries but fails to capture the generality of the relational model.<p>So, from a language development point of view, one has to ask: Is this special case worth the extra syntactical sugar? It has the downside, that when a query evolves and falls out of the special case you have to reformulate the syntactical sugar yourself. This creates friction, and it is still necessary to understand the relational model.<p>Other commenters mentioned Neo4j as an example where similar ideas have been implemented. From my limited experience with Neo4j, I'd say it makes a lot of sense there, because graph queries will often fall into the sub class of queries, that can benefit from the syntactical sugar.<p>All in all, I would not call this a simplification. Syntactical sugar never is a simplification. It is an "easification". It makes certain examples easy and hides what is going on, without really abstracting it away.
“As all joins involve the primary key…”<p>Often but not always the case<p>Edit: at least not in SQL, and therefore SQL databases. If the language being described here isn't actually SQL, that could still be a problem.
This implicit JOIN feature is already present in HQL and also in Jooq. It's very convenient, especially when you use a star-like table structure where the important data table is basically just IDs and references.<p><a href="https://www.jooq.org/doc/latest/manual/coming-from-jpa/from-jpa-implicit-join/" rel="nofollow">https://www.jooq.org/doc/latest/manual/coming-from-jpa/from-...</a>
I think it is important to realize that relational, object-oriented and structured data (such as XML and JSON) are all implementation of an often far more richer data model. Would it not be better to implement something for the richer data model, such that querying relational data models to produce JSON (for example) would become trivial?<p>A long time ago, I made some attempts about this: <a href="https://www.iwriteiam.nl/AoP_spec_stat.html" rel="nofollow">https://www.iwriteiam.nl/AoP_spec_stat.html</a> Not so long ago, I worked a bit on a data oriented language with cursors, compounds and components: <a href="https://github.com/FransFaase/DataLang">https://github.com/FransFaase/DataLang</a>.
Cool idea! I always thought this was the purpose of cypher match statements but your syntax is much shorter and more familiar dot-pathing<p>One idea would be for the x.y.z to use functions like<p>x.y(argument).z<p>This way you could parameterize the traversals and it’d wind up looking like gremlin in sql
> Both tables use id field as their primary keys. Managers are also employees, so the two tables share the ids. Since managers have more attributes, their information is stored in a separate table.<p>> Now we want to find the total income (including the allowance) of each employee (including every manager).<p>> A JOIN operation is necessary for SQL to do it:<p><pre><code> > SELECT employee.id, employee.name, employy.salary+manager.allowance
> FROM employee
> LEFT JOIN manager ON employee.id=manager.id
</code></pre>
> But for two tables having a one-to-one relationship, we can treat them like one table:<p><pre><code> > SELECT id,name,salary+allowance
> FROM employee
</code></pre>
What about employees who aren't managers? I assume they have no entry in the manager table. The SQL would ignore them, because it's a left join, which is not what was asked for. Does the proposed query do the same?<p>What happens if there is also<p><pre><code> salesperson table
id
allowance
</code></pre>
? Which table is joined?<p>This language seems a little half-baked.
A 30 year old implementation of the same idea (even slightly better IMHO) can be found in kdb+/ksql/shakti, you can see examples and comparison in <a href="https://shakti.com/" rel="nofollow">https://shakti.com/</a> (press document, then sql.d - no idea how to link directly). And yes, it is by Arthur Whitney and related to the [in]famous K
jOOQ has similar feature<p><a href="https://www.jooq.org/doc/latest/manual/code-generation/codegen-advanced/codegen-config-generate/codegen-implicit-join-paths/" rel="nofollow">https://www.jooq.org/doc/latest/manual/code-generation/codeg...</a>
Didn't <a href="https://www.odata.org/" rel="nofollow">https://www.odata.org/</a> do this already?
If the Odata server understands the table relations, you can navigate through table relations in the REST query.
I found that most parts of the business prefer filtering more than joining, learning another DSL other than SQL was a real barrier, and that many BI tools only support a subset of Odata, so are pretty much useless.<p>I definitely prefer concatenative (monadic) syntax a la Linq though, as it allows better scoping of efficient joins without a planner - it allows you to duck-tape (allusion intended) together a platform service easily.
SOQL in Salesforce supports "relationship queries" that works on both directions, viz, parent-to-child and child-to-parent.<p><a href="https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_examples.htm" rel="nofollow">https://developer.salesforce.com/docs/atlas.en-us.soql_sosl....</a>
More geniuses trying to "improve" SQL, and yet the only party to succeed in doing this is Postgres people, of actual relational SQL database—by means of dialect.