From: | "Joel Jacobson" <joel(at)compiler(dot)org> |
---|---|
To: | "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "Isaac Morland" <isaac(dot)morland(at)gmail(dot)com> |
Cc: | "Corey Huinker" <corey(dot)huinker(at)gmail(dot)com>, "PostgreSQL Developers" <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Foreign key joins revisited |
Date: | 2021-12-28 19:26:36 |
Message-ID: | a9a9c45b-7c90-45dc-bdcf-7b3b32a97d41@www.fastmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Mon, Dec 27, 2021, at 19:15, Tom Lane wrote:
> NATURAL JOIN is widely regarded as a foot-gun that the SQL committee
> should never have invented. Why would we want to create another one?
>
> (I suspect that making the constraint name optional would be problematic
> for reasons of syntax ambiguity, anyway.)
I agree. I remember this blog post from 2013 discussing the problems
with both NATURAL but also the problems with USING:
http://www.databasesoup.com/2013/08/fancy-sql-monday-on-vs-natural-join-vs.html
Since my last email in this thread, I've learned KEY is unfortunately not a reserved keyword.
This probably means the proposed "JOIN KEY" would be problematic, since a relation could be named KEY.
Can with think of some other suitable reserved keyword?
How about JOIN WITH?
join_type JOIN WITH fk_table.fk_name [ [ AS ] alias ]
join_type JOIN fk_table [ [ AS ] alias ] WITH fk_name REF pk_table
FROM permission p
LEFT JOIN WITH p.permission_role_id_fkey r
LEFT JOIN team_role tr WITH team_role_role_id_fkey REF r
LEFT JOIN WITH tr.team_role_team_id_fkey t
LEFT JOIN user_role ur WITH user_role_role_id_fkey REF r
LEFT JOIN WITH ur.user_role_user_id_fkey u
WHERE p.id = 1;
/Joel
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2021-12-28 19:41:04 | Re: Foreign key joins revisited |
Previous Message | Matheus Alcantara | 2021-12-28 19:26:32 | Re: [PROPOSAL] Make PSQLVAR on \getenv opitional |