5 ms·

Simplifying Join Syntax

53 points by Judyrabbit 2y ago

asplake 2y ago
“As all joins involve the primary key…” Often but not always the case 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.
bionhoward 2y ago
Cool idea! I always thought this was the purpose of cypher match statements but your syntax is much shorter and more familiar dot-pathing One idea would be for the x.y.z to use functions like x.y(argument).z This way you could parameterize the traversals and it’d wind up looking like gremlin in sql
- Judyrabbit 2y ago
  x.y(argument).z should be OK in syntax, but it is hard to implement. we can not predict which data type y(argument) would return, and if it can perform dot operation further. We have implemented DQL(The syntax mentioned here) by translating to SQL. But we priovide only Chinese version now, we will release the English version serveral months later, please keep an eye out if you're interested
PeterZaitsev 2y ago
There are many good ideas to improve SQL, though it is not easy to get traction as it is such an old standard with so many implementations
beagle3 2y ago
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 https://shakti.com/ https://shakti.com/ (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
- ghusbands 2y ago
  The direct link is https://shakti.com/document/sql.d https://shakti.com/document/sql.d
tucnak 2y ago
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.
david_p 2y ago
I love the idea of a simplified SQL syntax for joins. 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. 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. 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. Anyway, if you wanna learn more about GQL, that a look at https://www.gqlstandards.org/ https://www.gqlstandards.org/
- aidos 2y ago
  I struggled to find the actual language at the link you posted - is there an obvious example somewhere? Given that graphql is often shortened to gql is feel this is going to get confusing!
  - kirmerzlikin 2y ago
    This paper [1] explains some basics [1] https://arxiv.org/abs/2112.06217 https://arxiv.org/abs/2112.06217
- twic 2y ago
  > I must point out that Neo4j was the first to propose a syntax that made traversal feel simple and natural again Was it the first? Does it predate path expressions in Hibernate Query Language / Java Persistence Query Language?
- atombender 2y ago
  I think InterSystems Caché did it first. Its SQL join syntax lets you do `WHERE employee->manager->manager->name`. I'm not sure it has arbitrary recursion like you can do with graph databases, however.
- ghusbands 2y ago
  Graph databases aren't particularly relevant to a novel syntax for relational databases. The part of the related standard that is about relational databases (SQL/PGQ) demands that every table hold either edges or vertices, but not both [1], which is very limiting. [1] https://peter.eisentraut.org/blog/2023/04/04/sql-2023-is-finished-here-is-whats-new https://peter.eisentraut.org/blog/2023/04/04/sql-2023-is-fin...
maweki 2y ago
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. https://www.jooq.org/doc/latest/manual/coming-from-jpa/from-jpa-implicit-join/ https://www.jooq.org/doc/latest/manual/coming-from-jpa/from-...
- twic 2y ago
  Yes, this is a lot like path expressions in HQL / JPQL. There is a key difference, i think - in JPA land, relationships between classes are named at both ends. So an OrderDetail has an "Order order" field, but an Order also has a "Set<OrderDetail> details" field. That means you already have a name to use when going from an order to its details - you can say "sum(order.details.price)". Whereas the language in the article has to make it implicit, with (IMHO weird!) syntax like "OrderDetail.sum(price)". This starts to hurt more when you have multiple collections of the same type on an entity. Consider: City city_id Person birth_city_id residence_city_id What would SELECT city_id, Person.COUNT() FROM City do here?
pavi2410 2y ago
SOQL in Salesforce supports "relationship queries" that works on both directions, viz, parent-to-child and child-to-parent. https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_examples.htm https://developer.salesforce.com/docs/atlas.en-us.soql_sosl....
fjfaase 2y ago
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? A long time ago, I made some attempts about this: https://www.iwriteiam.nl/AoP_spec_stat.html https://www.iwriteiam.nl/AoP_spec_stat.html Not so long ago, I worked a bit on a data oriented language with cursors, compounds and components: https://github.com/FransFaase/DataLang https://github.com/FransFaase/DataLang.
funcDropShadow 2y ago
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. 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. 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. 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.
- shiandow 2y ago
  They explain 1:N relations in the article, so I won't repeat that here. A N:M relation will be presented the same way as in all other relational databases, it requires a separate table with rows containing both keys. And if I'm honest I think this captures more of the relational model than SQL does, you might be confusing the two. This synctactic sugar is actually using the properties relations have, rather than SQL which allows you to compute all kinds of things whether they make sense or not. Note that they talk about using foreign keys for this purpose. Really what this is doing is turning the relation formed by this column and the primary key into a function (see [1] for an article I wrote on the subject), which allows for some nicer syntax because functions are nicer than general relations. This means most of the problems you mention can be resolved through simply enforcing that constraint. In a sense this moves the problem, but it does mean you can't accidentally invalidate the query. And frankly having some syntactic sugar for foreign keys in SQL is a feature that's long overdue. The main downside is the inability to do anything other than equijoins, and the inability to specify new relations on the fly. The latter is a bit of a problem, but not insurmountable. I can't figure out a reasonable way to do anything other than equijoins, but that might be for the best. Also, ironically what I'm really missing is how to do an actual join, it's nice to explicitly specify functions but if you've got two foreign keys to the same table (functions with the same codomain) how do you calculate the join (pullback)? [1]: https://pragmathics.nl/2023/10/24/putting-the-relational-back-in-relational-databases/#functions https://pragmathics.nl/2023/10/24/putting-the-relational-bac...
atemerev 2y ago
See “natural join”, which already exists in SQL; I use it all the time.
- doctor_eval 2y ago
  Yeah I thought natural join was the same, but I keep forgetting to use it!
- ghusbands 2y ago
  Natural joins are terrible in that they match on any equal column names. Let's hope you don't have employees with names and departments with names. In any case, even with natural join syntax, you end up with queries longer than those in the article.
zeronone 2y ago
jOOQ has similar feature https://www.jooq.org/doc/latest/manual/code-generation/codegen-advanced/codegen-config-generate/codegen-implicit-join-paths/ https://www.jooq.org/doc/latest/manual/code-generation/codeg...
- ghusbands 2y ago
  Many ORMs have similar features - jOOQ is not unique in this. The article is talking about an addition to SQL syntax, not a DSL embedded in another language.
alexisread 2y ago
Didn't https://www.odata.org/ https://www.odata.org/ 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. 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.
twic 2y ago
> 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. > Now we want to find the total income (including the allowance) of each employee (including every manager). > A JOIN operation is necessary for SQL to do it: > SELECT employee.id, employee.name, employy.salary+manager.allowance > FROM employee > LEFT JOIN manager ON employee.id=manager.id > But for two tables having a one-to-one relationship, we can treat them like one table: > SELECT id,name,salary+allowance > FROM employee 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? What happens if there is also salesperson table id allowance ? Which table is joined? This language seems a little half-baked.

53 points by Judyrabbit 2y ago

“As all joins involve the primary key…” Often but not always the case 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.

bionhoward 2y ago

Cool idea! I always thought this was the purpose of cypher match statements but your syntax is much shorter and more familiar dot-pathing One idea would be for the x.y.z to use functions like x.y(argument).z This way you could parameterize the traversals and it’d wind up looking like gremlin in sql

Judyrabbit 2y ago
x.y(argument).z should be OK in syntax, but it is hard to implement. we can not predict which data type y(argument) would return, and if it can perform dot operation further. We have implemented DQL(The syntax mentioned here) by translating to SQL. But we priovide only Chinese version now, we will release the English version serveral months later, please keep an eye out if you're interested

PeterZaitsev 2y ago

There are many good ideas to improve SQL, though it is not easy to get traction as it is such an old standard with so many implementations

beagle3 2y ago

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 https://shakti.com/ https://shakti.com/ (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

ghusbands 2y ago
The direct link is https://shakti.com/document/sql.d https://shakti.com/document/sql.d

tucnak 2y ago

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.

david_p 2y ago

I love the idea of a simplified SQL syntax for joins. 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. 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. 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. Anyway, if you wanna learn more about GQL, that a look at https://www.gqlstandards.org/ https://www.gqlstandards.org/

aidos 2y ago
I struggled to find the actual language at the link you posted - is there an obvious example somewhere? Given that graphql is often shortened to gql is feel this is going to get confusing!
- kirmerzlikin 2y ago
  This paper [1] explains some basics [1] https://arxiv.org/abs/2112.06217 https://arxiv.org/abs/2112.06217
twic 2y ago
> I must point out that Neo4j was the first to propose a syntax that made traversal feel simple and natural again Was it the first? Does it predate path expressions in Hibernate Query Language / Java Persistence Query Language?
atombender 2y ago
I think InterSystems Caché did it first. Its SQL join syntax lets you do `WHERE employee->manager->manager->name`. I'm not sure it has arbitrary recursion like you can do with graph databases, however.
ghusbands 2y ago
Graph databases aren't particularly relevant to a novel syntax for relational databases. The part of the related standard that is about relational databases (SQL/PGQ) demands that every table hold either edges or vertices, but not both [1], which is very limiting. [1] https://peter.eisentraut.org/blog/2023/04/04/sql-2023-is-finished-here-is-whats-new https://peter.eisentraut.org/blog/2023/04/04/sql-2023-is-fin...

maweki 2y ago

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. https://www.jooq.org/doc/latest/manual/coming-from-jpa/from-jpa-implicit-join/ https://www.jooq.org/doc/latest/manual/coming-from-jpa/from-...

twic 2y ago
Yes, this is a lot like path expressions in HQL / JPQL. There is a key difference, i think - in JPA land, relationships between classes are named at both ends. So an OrderDetail has an "Order order" field, but an Order also has a "Set<OrderDetail> details" field. That means you already have a name to use when going from an order to its details - you can say "sum(order.details.price)". Whereas the language in the article has to make it implicit, with (IMHO weird!) syntax like "OrderDetail.sum(price)". This starts to hurt more when you have multiple collections of the same type on an entity. Consider: City city_id Person birth_city_id residence_city_id What would SELECT city_id, Person.COUNT() FROM City do here?

pavi2410 2y ago

SOQL in Salesforce supports "relationship queries" that works on both directions, viz, parent-to-child and child-to-parent. https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_examples.htm https://developer.salesforce.com/docs/atlas.en-us.soql_sosl....

fjfaase 2y ago

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? A long time ago, I made some attempts about this: https://www.iwriteiam.nl/AoP_spec_stat.html https://www.iwriteiam.nl/AoP_spec_stat.html Not so long ago, I worked a bit on a data oriented language with cursors, compounds and components: https://github.com/FransFaase/DataLang https://github.com/FransFaase/DataLang.

funcDropShadow 2y ago

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

shiandow 2y ago
They explain 1:N relations in the article, so I won't repeat that here. A N:M relation will be presented the same way as in all other relational databases, it requires a separate table with rows containing both keys. And if I'm honest I think this captures more of the relational model than SQL does, you might be confusing the two. This synctactic sugar is actually using the properties relations have, rather than SQL which allows you to compute all kinds of things whether they make sense or not. Note that they talk about using foreign keys for this purpose. Really what this is doing is turning the relation formed by this column and the primary key into a function (see [1] for an article I wrote on the subject), which allows for some nicer syntax because functions are nicer than general relations. This means most of the problems you mention can be resolved through simply enforcing that constraint. In a sense this moves the problem, but it does mean you can't accidentally invalidate the query. And frankly having some syntactic sugar for foreign keys in SQL is a feature that's long overdue. The main downside is the inability to do anything other than equijoins, and the inability to specify new relations on the fly. The latter is a bit of a problem, but not insurmountable. I can't figure out a reasonable way to do anything other than equijoins, but that might be for the best. Also, ironically what I'm really missing is how to do an actual join, it's nice to explicitly specify functions but if you've got two foreign keys to the same table (functions with the same codomain) how do you calculate the join (pullback)? [1]: https://pragmathics.nl/2023/10/24/putting-the-relational-back-in-relational-databases/#functions https://pragmathics.nl/2023/10/24/putting-the-relational-bac...

atemerev 2y ago

See “natural join”, which already exists in SQL; I use it all the time.

doctor_eval 2y ago
Yeah I thought natural join was the same, but I keep forgetting to use it!
ghusbands 2y ago
Natural joins are terrible in that they match on any equal column names. Let's hope you don't have employees with names and departments with names. In any case, even with natural join syntax, you end up with queries longer than those in the article.

zeronone 2y ago

jOOQ has similar feature https://www.jooq.org/doc/latest/manual/code-generation/codegen-advanced/codegen-config-generate/codegen-implicit-join-paths/ https://www.jooq.org/doc/latest/manual/code-generation/codeg...

ghusbands 2y ago
Many ORMs have similar features - jOOQ is not unique in this. The article is talking about an addition to SQL syntax, not a DSL embedded in another language.

alexisread 2y ago

Didn't https://www.odata.org/ https://www.odata.org/ 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. 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.

twic 2y ago

> 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. > Now we want to find the total income (including the allowance) of each employee (including every manager). > A JOIN operation is necessary for SQL to do it: > SELECT employee.id, employee.name, employy.salary+manager.allowance > FROM employee > LEFT JOIN manager ON employee.id=manager.id > But for two tables having a one-to-one relationship, we can treat them like one table: > SELECT id,name,salary+allowance > FROM employee 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? What happens if there is also salesperson table id allowance ? Which table is joined? This language seems a little half-baked.