JOIN Like an ORM: Foreign-Key Relations in PostgreSQL

A PostgreSQL query that navigates relations:

SELECT client.name, profile_detail.phone, delivery_address.city, item.name, quantity
FROM document
, client(document)
, profile_detail(client)
, delivery_address(document)
, document_item_list(document)
, item(document_item_list)
WHERE document.doc_number = 'DOC-1'

The planner turns it into exactly the query you would have written by hand (dbfiddle):

SELECT p.name, pd.phone, a.city, i.name, di.quantity
FROM document d
JOIN profile p ON p.client_id = d.client_id
JOIN profile_detail pd ON pd.profile_id = p.id
JOIN address a ON (a.id, a.profile_id) = (d.delivery_address_id, d.client_id)
JOIN document_item di ON di.document_id = d.id
JOIN item i ON i.id = di.item_id
WHERE d.doc_number = 'DOC-1'

(profile here is a shared people table with a unique client_id column, and the delivery address is referenced by a composite key; the full demo schema is on the diagram below.)

This is vanilla Postgres — no C extension, no fork, no new syntax. Below: why the SQL standard never managed this in forty years (joins are older than foreign keys themselves), who tried to push it through — from the draft standards of the nineties to a patch sitting in pgsql-hackers right now — and how to switch this navigation on in Postgres today.

The schema knows the relations. SQL makes you repeat them

A FOREIGN KEY is a machine-checked declaration of a relation: the columns, the target table, the cardinality. All of it sits in the catalog, and the overwhelming majority of real-world joins follow these declarations — one practitioner in an HN discussion put it at "95%+ of my joins". Yet every JOIN starts from a blank slate. Three problems follow:

• Verbosity. Five relations means five ON clauses restating the schema word for word.
• Silent mistakes. ON accepts any equality: ON d.id = di.item_id instead of document_id runs without complaint — the types match. The error surfaces in the data, not at compile time.
• Lost intent. Looking at ON a.x = b.y, you can't tell whether it's a relation or a coincidence, which side is the parent, or whether rows will multiply in an aggregate.

The pain is not new: the language began as SEQUEL in 1974 — joins were there from day one, as a comma in FROM. The first standard, SQL-86, shipped without referential integrity; foreign keys only arrived with the SQL-89 revision: join syntax is older than the relations themselves. Explicit JOIN ... ON appeared in SQL-92 — the committee designed a new join syntax with FKs already in plain sight, and still didn't connect the two. The standard didn't reach relation navigation in queries until SQL:2023 — and even then via graph patterns, not joins.

While the committee deliberated, the problem got solved elsewhere: relation navigation is the first thing every ORM offers — @relation in Prisma, navigation properties in Entity Framework, select_related in Django. Application code moved to ORMs largely for exactly this, putting up with N+1 queries and leaky abstractions along the way. The irony: the source of truth about relations was in the database all along. ORMs duplicate it, GraphQL layers introspect it — and only SQL itself pretends to know nothing.

The SQL sugar that didn't help

SQL has tried to shorten join conditions more than once. All four attempts missed the mark.

Comma + WHERE. The pre-sugar era: tables in a list, join conditions mixed into WHERE with the filters. Forget one condition — get a Cartesian product, silently.

JOIN ... ON. Today's mainstream. Explicit, flexible — and carrying exactly the three problems above: restatement, any equality, zero semantics.

JOIN ... USING (col). Shorter, but it joins on name coincidence: both tables must call the column the same thing. The supplier_id = supplier_id convention passes; the most common pattern, document_item.document_id → document.id, doesn't. FK constraints play no part in the decision at all.

NATURAL JOIN. The logical extreme: join on all same-named columns at once. Add an updated_at column — one the first table already has — to the second table, and the query silently starts joining on it too. NATURAL JOIN is what discredited the very idea of "automatic" joins — though the flaw was never the automation: the flaw was building on column names (an accident) instead of declared relations (an intent).

Forty years of the standard, and it still won't let you say "join along the declared relation". Either restate the schema by hand, or guess by names.

Who tried to improve SQL syntax

The idea of joining along a declared relation has been in the air for decades — and it has a well-populated graveyard.

SQL drafts, the 1990s. Draft versions of the standard contained JOIN ... USING PRIMARY KEY | USING FOREIGN KEY | USING CONSTRAINT syntax. According to Peter Eisentraut, "these ideas just faded away because of other priorities".

Sybase SQL Anywhere: KEY JOIN. The only DBMS that actually shipped it — and has kept it for decades (lineage: Watcom SQL → Sybase → SAP since 2010). FROM Products KEY JOIN SalesOrderItems joins along the declared FK; more than that, KEY JOIN is the default there — a bare JOIN with no ON means exactly this. Ambiguity between multiple FKs is resolved by convention: the constraint's role name must match the alias. It works, but the idea never escaped one niche DBMS.

PostgreSQL, the 2021 wave. Joel Jacobson brought the idea to pgsql-hackers twice: first as FK path expressions, then as "Foreign key joins revisited" — the syntax mutated for nine days (WITH p->fk = r → ON KEY p.fk → USING KEY) and drowned in bikeshedding. Tom Lane's verdict from that thread became a classic of the genre: "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?" A parallel JOIN FOREIGN gist drew ~200 comments on Hacker News — and every objection that has kept resurfacing ever since:

• multiple FKs between the same tables — ambiguity;
• a new FK in the schema silently changes (or breaks) existing queries;
• constraint names are a poor interface: ORMs generate unreadable ones;
• a query shouldn't depend on a constraint's existence — FKs get dropped for bulk loads and sharding.

jOOQ — the only workaround that reached mass production. The Java query builder implemented .onKey() and implicit path joins (BOOK.author().name() synthesizes the LEFT JOIN along the FK by itself). Tellingly, jOOQ stated the strongest objection itself, in its own manual: "The ON KEY clause can quickly produce ambiguities ... queries that have worked in the past ... will stop working" — and steered its users toward path joins, where the specific FK is pinned down by code generation.

PostgreSQL, the 2026 wave — happening right now. In May Jacobson came back with a team of co-authors, a working patch and a change proposal to the ISO committee: LEFT JOIN order_items oi FOR KEY (order_id) -> o (id) (interactive examples at keyjoin.org). The emphasis has shifted from saving keystrokes to correctness: a key join is a declaration that the join follows a referential path, and the database must prove it (the headline case being silent fan-out: a 1:N join quietly multiplies rows, and SUM() dutifully adds up the duplicates). Tomas Vondra's review was respectful but unsparing: a ~5200-line patch with, by his estimate, near-zero odds of being deemed committable, and a 30–40% planning regression. The thread is alive, the outcome unclear, the standardization timeline measured in years.

Oracle 26 — shipped, 2026. While the Postgres patch goes through review, Oracle simply shipped its own: JOIN TO ONE joins along the declared keys, with no ON at all.

SELECT o.order_id, p.product_name, c.cust_email, oi.quantity
FROM oe.order_items oi
JOIN TO ONE (oe.orders o, oe.product_information p, oe.customers c)

No constraint names are mentioned: Oracle resolves the path through the declared keys itself and requires it to be exactly one — ambiguity is not settled by convention, it is declared an error. And TO ONE in the name is itself a cardinality declaration: the join may widen the row, never multiply it. The same turn towards correctness as FOR KEY, except already in production and announced as a candidate for standardization.

Nothing has made it through the committee in thirty years: what reached production, each vendor shipped on its own terms — KEY JOIN in one, JOIN TO ONE in another, mutually incompatible. New syntax means grammar, parser, standard, backward compatibility and committee consensus; a vendor needs only the first two.

2026: the idea won everywhere — except SQL

While FK joins stall in committees, the idea itself — declare the relation once, then walk it — has won everywhere around SQL:

• Graph standards. SQL:2023 SQL/PGQ: declare a property graph over your tables, query it with patterns like MATCH (c IS customer)-[IS has_placed]->(o) — already in Oracle 23ai, committed to PostgreSQL 19 (March 2026, GA expected in the fall), in DuckDB via the DuckPGQ extension; alongside it, a separate ISO language, GQL (2024) — Neo4j, Spanner Graph. Note: even the standard didn't dare touch JOIN itself — relations were moved out into a separate graph layer with its own syntax.
• Semantic models. Snowflake Semantic Views (GA 2025): RELATIONSHIPS (orders (customer_id) REFERENCES customers (customer_id)) is declared in a schema object, the query names dimensions and metrics — the engine generates the joins. Malloy took the same thought all the way to a language: join_one: users with user_id in the model, then just the path users.name.
• Layers on top of the database. Here FK navigation has long been the norm. PostgREST assembles nested responses straight from FKs (?select=title,directors(last_name)). PostGraphile builds an entire GraphQL schema out of FKs — a pair of fields per relation (personByAuthorId / postsByAuthorId), 1:1 via UNIQUE — and compiles the query into a single SQL statement with json_agg. And Supabase's pg_graphql executes GraphQL with a single SQL function, graphql.resolve(...), right inside the database.

Many independent systems converged on the same model: an FK becomes a pair of navigations, one per direction, with 1:1 derived from uniqueness. That's convergent evolution: foreign key metadata is sufficient for a complete navigation model; the only question is which language to expose it in. So far the industry's answer has been "any language except SQL itself". pg_graphql takes the irony all the way: FK navigation already lives inside your Postgres — it just speaks GraphQL.

And one telling data point: pipe syntax — the loudest SQL reform of recent years (BigQuery, Spark 4.0) — rearranged everything except joins: |> JOIN requires the same old ON.

The solution: a relation is a function

Now for the trick: relation navigation in Postgres needs neither new syntax nor a patch — nor even a recent version. Functions taking a table row and attribute notation (calling a function with a dot: document.client instead of client(document)) go back to Postgres' object-relational roots, and the planner has inlined set-returning SQL functions since 8.4 — that's 2009 — the technique should work even on versions long out of support. All it takes is turning each FK into a pair of functions: lookup — follow the reference, and list — collect the referencing rows. Here's the demo schema, every edge labeled with its pair:

The same schema in SQL lives in example/init.sql in the repository, and, complete with pre-generated relation functions, in a db<>fiddle sandbox.

For a single relation it looks like this. The lookup — a document's client:

CREATE FUNCTION client(document) RETURNS SETOF profile LANGUAGE sql STABLE PARALLEL SAFE
AS $$ SELECT * FROM public.profile WHERE (client_id) = (($1).client_id) $$;

The list — all of a client's documents:

CREATE FUNCTION client_document_list(profile) RETURNS SETOF document LANGUAGE sql STABLE PARALLEL SAFE
AS $$ SELECT * FROM public.document WHERE (client_id) = (($1).client_id) $$;

Why functions rather than views? A VIEW freezes one particular join, not a relation: a view doesn't take a row, doesn't compose into chains like client(document(di)), doesn't overload on argument type — and inside it still lives that same hand-written ON. The function is the relation, its argument is the model: an edge of the graph you can step along from anywhere in a query.

In FROM such a function behaves like an ordinary table. No LATERAL needed: functions see their FROM neighbors anyway — the documentation itself calls it a noise word. A required relation (NOT NULL FK column) joins with a comma. An optional one (nullable) takes LEFT JOIN ... ON true: the join condition is already baked into the function, and ON true is there only because there is no LEFT JOIN without ON:

SELECT d.doc_number, p.name, m.name manager_name
FROM document d, client(d) p
LEFT JOIN manager(d) m ON true

Named for what it means

Simplicity won here: a function is named for what the relation means — and the meaning is already written into the FK column by its author. client_id yields client(document); the list function is named after its own table — document_item_list(document). Several relations to the same target split by role: client / manager. No naming theory: you don't recall the names, you guess them. The full rule set lives in Naming rules — defaults, kept in one place.

It's free

The key question for any "magic" is the price. The answer: indistinguishable from a hand-written join. When a relation function sits in FROM, the planner inlines it completely: EXPLAIN matches the classic query node for node — same Nested Loop / Hash Join, same indexes, same parallel workers. On a test database of 300k documents and 1.35M line items, a heavy aggregate over a four-table chain: medians of five runs — 576 ms classic vs 571 ms with functions; the "winner" flips between individual runs, and with identical plans all that's left is inter-run noise. Plan walkthroughs for the "classic vs relations" pairs live in EXPLAIN.md.

And this is no return to the navigational databases Codd steered the industry away from in 1970: under the sugar it's the same relational algebra — the plan is identical to the hand-written join, and navigation mixes freely with classic joins in the same query.

The generator guarantees the inlining conditions itself: LANGUAGE sql, STABLE, not STRICT, not SECURITY DEFINER, and — the non-obvious one, learned by measurement — PARALLEL SAFE: query parallelism is decided before inlining, and the default PARALLEL UNSAFE silently disables workers for the whole query (that cost us ~1280 ms vs 571 ms). Drift is caught out of the box: the generator (more on it below) checks every function's body and its flags against the reference definition, and a function recreated by hand or missing PARALLEL SAFE shows up in its report.

The generator: one function for everything

Writing these functions by hand would be restating the schema all over again, just from a different angle. So the database writes them itself. The whole thing is called pg_relation_sql and is a single self-installing SQL file, relation_sql.sql — no extensions, no access to the server filesystem, PostgreSQL 11+ is enough. Installing is one command:

curl -sf https://raw.githubusercontent.com/asmgit/pg_relation_sql/main/relation_sql.sql | psql postgresql://postgres:postgres@localhost:5432/postgres

Loading the file creates the functions, adds the event trigger and prints the dashboard in response.

Inside the file is a single function, relation_sql(mode): it reads pg_constraint and brings the set of relation functions in line with the schema.

SELECT status, command FROM relation_sql();

 pg_relation_sql 0.1.0 — relation functions generated from foreign keys | SELECT status, command FROM relation_sql()
 event trigger: installed                                               | SELECT status, command FROM relation_sql('uninstall')
 relation functions: 16 ok, 0 to sync, 0 foreign, 0 duplicate           | SELECT status, command FROM relation_sql('drop')
 details                                                                | SELECT * FROM relation_sql('show')

'show' is a per-FK diff with ready-to-run SQL; 'sync' applies it once (a migration step); 'install' sets up an event trigger, after which the functions follow the DDL on their own: a CREATE TABLE with an FK spawns its pair of functions right inside the command, a DROP CONSTRAINT removes them. It's built to be careful: a failed sync never breaks your DDL, a foreign function with the same name is never touched, and an unresolvable name collision goes to a human instead of a silent overwrite. Modes and statuses in detail: README.

A team with CI migrations is better served by the other path — not the trigger, but relation_sql('sync') as a migration step: functions are born in the same pipeline as the rest of the DDL, go through review, and reproduce deterministically in every environment. The trigger is the mode for sandboxes, solo projects and databases where one person does all the DDL anyway. pg_dump/restore round-trips cleanly in both modes: functions travel as ordinary objects, and the event trigger is restored last, so it doesn't fire mid-restore (verified).

What it buys you in queries

Beyond the vanished ON clauses — idioms the classic style doesn't have; five favorites (the anti-join's cost on full-table scans is covered in the limitations):

-- revenue per client: three hops, zero ON
SELECT p.name, sum(di.quantity * i.price) revenue
FROM profile p, client_document_list(p) d, document_item_list(d) di, item(di) i
GROUP BY p.id
;
-- deliveries to Berlin: the composite FK stays hidden
SELECT d.doc_number, a.street
FROM document d, delivery_address(d) a
WHERE a.city = 'Berlin'
;
-- the whole related row as a field
SELECT doc_number, (document.client).* FROM document
;
-- profiles without documents
SELECT name FROM profile
WHERE NOT EXISTS (SELECT FROM client_document_list(profile))
;
-- recursive tree walk: the row travels as a value, the relation makes the step
WITH RECURSIVE tree AS (
  SELECT item node FROM item WHERE parent_item_id IS NULL
  UNION ALL
  SELECT c node FROM tree t, item_list(t.node) c
)
SELECT (node).name FROM tree
;

Recursive tree walks, chains, nested JSON, top-N, DML, INTERSECT/EXCEPT — all 19 "classic vs relations" pairs are in example/query.sql.

Honest limitations

A tool can only be trusted if it knows its boundaries. Ours:

• NOT EXISTS over a function never becomes an anti-join. The planner turns EXISTS sublinks into semi/anti-joins before inlining functions, so NOT EXISTS (SELECT FROM client_document_list(profile)) stays a correlated SubPlan forever — an index probe per outer row. With a selective filter there's no difference; write full-table anti-joins the classic way (our measurement: ~96 ms vs ~40 ms on 100k×300k).
• Attribute notation in a select list doesn't inline — the plan shows a ProjectSet, one call per row; and the semantics are INNER: a row whose relation is empty disappears. It's sugar for targeted lookups; heavy queries should navigate in FROM. (And this is not ORM-style N+1: there's a single round-trip, everything happens inside one query — only the plan shape differs.)
• DROP TABLE requires CASCADE — relation functions depend on the table's row type; leftovers on the other side of the relation are cleaned up by the trigger or the next sync. Framework-generated migrations that expect a bare DROP TABLE will stumble here — drop the table's functions explicitly before removing it ('show' provides the ready-made SQL).
• A new FK renames its neighbors. A second FK to the same table adds role prefixes: document_list becomes client_document_list, and existing queries fail with a compile error — the very KEY JOIN objection from 2021, except here the breakage is loud, and 'show' immediately lists the new names.
• No constraint, no navigation. As with every FK-based approach: dropping the FK removes the function, and dependent queries fail with a compile error — which is more honest than a silently changed result.
• The function body is SELECT *, so column-level privileges (GRANT SELECT (id, name)) won't coexist with relation functions: after inlining the query needs the whole row. RLS, by contrast, composes cleanly — policies apply to the table after inlining.
• You get twice as many functions as FKs, and they live in the schemas of their tables. \df gets noisier, autocomplete longer; IDE tooling and schema-diff tools (migra, atlas) alike can filter by the pg_relation_sql COMMENT marker — or better, manage them through the regular sync migration step, and to your tools they become ordinary managed DDL.
• The event trigger requires superuser. Without the rights install doesn't fail: the trigger step is skipped with a WARNING and the sync still runs — you get the functions, just not the DDL auto-tracking.
• It's a dialect. A query with client(d) only runs where the functions exist — same as with views. IDEs autocomplete them as ordinary catalog functions but give no semantic hint that "this is a relation"; the reader needs one new idea — "a function over a row is a relation". That's the price of any sugar; in return the relation is verified by compilation, not by a reviewer's eyeballs.
• It's Postgres-only. But patch-free: plain SQL on top of a vanilla database.

The sugar that fell just short

Three small items to round out the limitations: everything already works, but it could be nicer. Unlike the "big" syntax waves from the history above, these are small candidates for pgsql-hackers:

1. LEFT JOIN manager(document) without ON true. The condition already lives inside the function; ON true is pure grammatical noise, but it cannot be omitted.
2. Attribute notation in FROM. It would be even more expressive: FROM document_item, document_item.document, document.client — today that's a syntax error.
3. Inline functions before the EXISTS transformation. If the planner swapped the phase order, the first limitation above would disappear along with the ~96/~40 ms gap.

There's some irony in an article about a "no core changes needed" solution ending in a wishlist for the core. But that's the honest bottom line: ninety percent of the problem is covered by what Postgres already has — and the remaining ten would cost three small patches, not ~5200 lines of key-join patch.

SQL is getting friendlier

This story is part of a bigger wave. The standard takes its time — the dialects don't wait: DuckDB friendly SQL means FROM tbl before SELECT, GROUP BY ALL, reusable expression aliases and a dozen more simplifications.

SELECT * EXCLUDE (...) deserves a special mention. A confession: our own generator lists twenty-one columns three times over — only because Postgres has no way to take * and exclude a couple of columns from it. Listing columns is the same disease as listing join conditions: restating what the schema already knows. DuckDB cured it for columns. We cured it for relations. For columns, Postgres has no cure yet.

Brevity as physics: an argument for the AI era

The final argument is no longer about humans. SQL today is written and read at scale by language models, and for them brevity isn't aesthetics — it's physics: fewer context tokens, less surface for hallucinations, easier review by a human. Join conditions are the prime source of silent generation errors: a model writes ON d.id = di.item_id instead of document_id, the query runs, the report adds up — almost always. document(di) eliminates this class of errors structurally: either the relation exists and the condition is correct by construction, or it's a compile error. (Cardinality — required relation or optional — stays with the author, as in the classic style: the function verifies the path, not the NOT NULL.) And an agent gets the entire navigation vocabulary in one query — SELECT * FROM relation_sql('show') returns every relation in the schema with names and directions, down to a map like document → client with a ready-made SELECT * FROM document, client(document) per relation.

For decades SQL was compressed for the sake of humans — with dialects, ORMs, tooling around the database. Now the same compression has become infrastructure for machines.

Try it in a minute

The whole solution is pg_relation_sql: a single self-installing SQL file for PostgreSQL 11+ that turns every foreign key into a pair of relation functions and keeps them in sync with the schema.

No install at all — a db<>fiddle sandbox: the demo schema, pre-generated relation functions, six navigation examples and the two plans side by side, right in the browser.

For the real thing, the repository has a Docker environment: a demo schema covering every relation kind (example/init.sql), those 19 query pairs (example/query.sql) and bulk data of 300k documents and 1.35M line items (test/bigdata.sql) for realistic plans (walkthroughs in EXPLAIN.md):

git clone https://github.com/asmgit/pg_relation_sql.git
cd pg_relation_sql/test && docker compose up -d

The schema knows its relations. All that's left is to start asking it.

Questions, objections, and real-world schemas are all welcome: if the naming stumbles on one of them, I'll tune the rules — they are defaults, and the more schemas they survive, the better they'll get.