Re: Idea: Avoid JOINs by using path expressions to follow FKs - Mailing list pgsql-hackers

From Vik Fearing
Subject Re: Idea: Avoid JOINs by using path expressions to follow FKs
Date
Msg-id f5770a13-61a6-603e-6a66-14c65eea9998@postgresfriends.org
Whole thread Raw
In response to Re: Idea: Avoid JOINs by using path expressions to follow FKs  ("Joel Jacobson" <joel@compiler.org>)
List pgsql-hackers
On 3/28/21 1:26 PM, Joel Jacobson wrote:
> 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.

In that case, I oppose this suggestion.

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

SQL is not an implicit language, though.

I wouldn't mind something like

  FROM a JOIN b WITH a_b_fk

or something.  That would be really nice when the keys are multicolumn.
But I don't want an implicit join like you're suggesting.
-- 
Vik Fearing



pgsql-hackers by date:

Previous
From: "Euler Taveira"
Date:
Subject: Re: Add docs stub for recovery.conf
Next
From: Andrew Dunstan
Date:
Subject: Re: [HACKERS] Custom compression methods