I suggest using the fact foreign keys are constraints with unique names,
and using these names to explicitly specify what column(s) to join
between the two foreign key tables.<p>In PostgreSQL [2], foreign key contraint names only need to be unique
per table, which allows using the foreign table "as is" as the
constraint name, which allows for nice short names.
In other databases, the names will just need to be a little longer.<p>Given this schema:<p><pre><code> CREATE TABLE baz (
id integer NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE bar (
id integer NOT NULL,
baz_id integer,
PRIMARY KEY (id),
CONSTRAINT baz FOREIGN KEY (baz_id) REFERENCES baz
);
CREATE TABLE foo (
id integer NOT NULL,
bar_id integer,
PRIMARY KEY (id),
CONSTRAINT bar FOREIGN KEY (bar_id) REFERENCES bar
);
</code></pre>
We could write a normal SQL query like this:<p><pre><code> SELECT
bar.id AS bar_id,
baz.id AS baz_id
FROM foo
JOIN bar ON bar.id = foo.bar_id
LEFT JOIN baz ON baz.id = bar.baz_id
WHERE foo.id = 123
</code></pre>
I suggest adding a new binary operator,
allowed anywhere where a table name is expected,
taking the table alias to join from as left operand,
and the name of the foreign kery contraint to follow as the right operand.<p>Perhaps "->" could be used for this purpose,
since it's currently not used by the SQL spec in the FROM clause.<p>This would allow rewriting the above query into this:<p><pre><code> SELECT
bar.id AS bar_id,
baz.id AS baz_id
FROM foo
JOIN foo->bar
LEFT JOIN bar->baz
WHERE foo.id = 123
</code></pre>
Where e.g. "foo->bar" means:<p><pre><code> follow the foreign key constraint named "bar" on the table/alias "foo"
</code></pre>
If the same join type is desired for multiple joins,
another idea is to allow chaining the operator:<p><pre><code> SELECT
bar.id AS bar_id,
baz.id AS baz_id
FROM foo
LEFT JOIN foo->bar->baz
WHERE foo.id = 123
</code></pre>
Which would cause both joins to be left joins.<p><pre><code> SELECT
bar.id AS bar_id,
baz.id AS baz_id
FROM foo
LEFT JOIN foo->bar->baz
WHERE foo.id = 123
</code></pre>
[1] <a href="https://scattered-thoughts.net/writing/against-sql/" rel="nofollow">https://scattered-thoughts.net/writing/against-sql/</a><p>[2] <a href="https://www.postgresql.org/" rel="nofollow">https://www.postgresql.org/</a>