Re: Idea: Avoid JOINs by using path expressions to follow FKs

On Sun, Mar 28, 2021, at 12:25, Vik Fearing wrote:
> On 3/27/21 9:27 PM, Joel Jacobson wrote:
> > Imagine if we could simply write the SQL query like this:
> >
> > SELECT DISTINCT od.order_id.customer_id.company_name
> > FROM order_details AS od
> > WHERE od.product_id.product_name = 'Chocolade';
> >
> > I took the inspiration for this syntax from SQL/JSON path expressions.
>
> This is a terrible idea, but let me explain why.
>
> First of all, neo4j claims they don't have joins because they actually
> don't have joins. The nodes point directly to the other nodes. Your
> proposal is syntactic sugar over a real join. The equivalent to neo4j
> would be somehow storing the foreign ctid in the column or something.
>
> Secondly, and more importantly IMO, graph queries are coming to SQL.
> They are mostly based on Cypher but not entirely because they amalgamate
> concepts from other graph query languages like Oracle's PGQ. The
> "common" syntax is called GQL (https://www.gqlstandards.org/) and it's
> in the process of becoming Part 16 of the SQL standard. The timeline on
> that website says August 2022 (it started in September 2019).
>
> If that timeline holds and ambitious people work on it (I already know
> one who is; not me), I would expect this to be in PostgreSQL 16 or 17.
> The earliest your proposal could get in is 15, so it's not that much of
> a wait.

I think you misunderstood my idea entirely.

It has absolutely nothing to do with graph query languages,
except the one similarity I mentioned, not having joins.

What I propose is a way to do implicit joins by following foreign keys,
thus improving the SQL query language, making it more concise for
the simple case when what you want to do is an INNER JOIN on a
single column on which there is a single FOREIGN KEY.

/Joel