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
