Re: Foreign key joins revisited

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