Re: Key joins

Hi Alexander,

Thanks for your interest in working on improving SQL joins.

On Sun, Aug 2, 2026, at 09:50, Alexander Melnikov wrote:
> While FOR KEY makes its way through review, a userspace data point: I
> generate a pair of set-returning SQL functions per FK —
> client(document) / client_document_list(profile) — and navigate
> relations through them:

I note that your approach is only a convenience to avoid having to
specify the columns between two base tables between there is a declared
referential constraint.

Similar to how a table in SQL is not always a base table, but can be the
result of a subquery, view, CTE, or a join, a "key join" is a concept
that is not always between two base tables.

Below is one example of a so called "fanout" join, that when rewritten
using your approach, is of course not caught, since the function cannot
possible know the join site where it is being called from in the query
tree.

See: https://keyjoin.org/#939a75a0-c735-4010-9d99-1804c5f093bc

-- Wrong at runtime: doubles each total (fan trap).
SELECT o.id,
SUM(oi.amount) AS item_total,
SUM(p.amount) AS payment_total
FROM orders AS o
LEFT JOIN order_items AS oi ON oi.order_id = o.id
LEFT JOIN payments AS p ON p.order_id = o.id
GROUP BY o.id;

id | item_total | payment_total
-----+------------+---------------
100 | 200.00 | 200.00
101 | 30.00 | 30.00
(2 rows)

-- Still wrong at runtime: doubles each total (fan trap).
SELECT o.id,
SUM(oi.amount) AS item_total,
SUM(p.amount) AS payment_total
FROM orders AS o
LEFT JOIN order_items_list(o) AS oi ON TRUE
LEFT JOIN payments_list(o) AS p ON TRUE
GROUP BY o.id;

id | item_total | payment_total
-----+------------+---------------
100 | 200.00 | 200.00
101 | 30.00 | 30.00
(2 rows)

When we instead try to rewrite it to FOR KEY syntax,
we avoid getting the wrong result and get an error instead:

-- Rejected at compile time.
SELECT o.id,
SUM(oi.amount) AS item_total,
SUM(p.amount) AS payment_total
FROM orders AS o
LEFT JOIN order_items AS oi FOR KEY (order_id) -> o (id)
LEFT JOIN payments AS p FOR KEY (order_id) -> o (id)
GROUP BY o.id;

-- ERROR: key join from referencing relation p to referenced relation o cannot be proven
-- LINE 7: LEFT JOIN payments AS p FOR KEY (order_id) -> o (id)
-- ^
-- DETAIL: Referenced columns o (id) are not proven unique. A preceding join may duplicate rows from referenced relation o.

I hope this helps. I'm thankful for your interest in this feature,
and I note you said:

> Not an argument against FOR KEY — syntax in core would be strictly better.

I just wanted to clarify the possible misunderstanding that this is more
than just checking that columns drill down to a matching referential
constraint.

/Joel