Key Joins: SQL joins that say what relationship they follow.

JOIN ... FOR KEY is a foreign-key-aware equijoin with a compile-time proof of correctness. If the DBMS can prove the join follows a real foreign key and will not silently lose or duplicate the rows it preserves, it prepares the query. Otherwise, it fails before execution — an invalid key join never gets through.

See the syntax See the three checks

The guarantee — if it compiles, it's correct. FOR KEY accepts a key join only when it can prove it correct against the definition, and rejects every one it cannot prove. So a key join that compiles preserves each referencing row exactly once and enriches it through the declared key — nothing invalid compiles.

The proof is conservative: it can reject a join your current data happens to satisfy, but it never accepts a wrong one.

The short version

What changes?

Key joins do not add a new runtime algorithm. They add a way for the query to declare, locally, that a join is following a referential relationship.

1Intent is visible

FOR KEY says this is not just matching values. It is following FK columns to referenced columns.

2The arrow is factual

The arrow always points from the referencing side to the referenced side, independent of table order.

3Only correct joins compile

The DBMS proves each key join from declarations and query structure, accepting it only if correct and rejecting every one it cannot prove.

4Runtime stays ordinary

Once accepted, the join runs like the equivalent equijoin. The new value is the compile-time contract.

Syntax

Before and after

The traditional join says two values are equal; the key join says which referential constraint the equality follows. Here are three tables — order_items, orders, customers — joined two ways, each with the same result.

From a line item, look up its order and customer — every join is <-

Plain equijoin 
SELECT c.name, o.order_date, oi.amount
FROM order_items AS oi
JOIN orders AS o    ON o.id = oi.order_id
JOIN customers AS c ON c.id = o.customer_id;
Key join 
SELECT c.name, o.order_date, oi.amount
FROM order_items AS oi
JOIN orders AS o    FOR KEY (id) <- oi (order_id)
JOIN customers AS c FOR KEY (id) <- o (customer_id);

From a customer, expand to orders and line items — every join is ->

Plain equijoin 
SELECT c.name, o.order_date, oi.amount
FROM customers AS c
JOIN orders AS o       ON o.customer_id = c.id
JOIN order_items AS oi ON oi.order_id = o.id;
Key join 
SELECT c.name, o.order_date, oi.amount
FROM customers AS c
JOIN orders AS o       FOR KEY (customer_id) -> c (id)
JOIN order_items AS oi FOR KEY (order_id) -> o (id);

Why name a table after the arrow? With a single join it looks redundant — but real queries chain several, and each arrow has to say which table already in scope this join attaches to.

Same query, plus a proof. Each FOR KEY clause is exactly the equijoin beside it — e.g. o.id = oi.order_id — with the same output columns. It only adds the compile-time proof that the join follows that foreign key.

<- The newly joined table is the referenced side, when enriching a row with lookup data.
-> The newly joined table is the referencing side, when expanding from one row to its children.

Result shape

Which way the arrow points decides the row count

Either direction keeps every referencing row exactly once. They differ in what happens to the table already in the query: a lookup, or a fan-out.

<-  enrich — row count unchanged

rows so far

▲
●
▲
→FOR KEY <-

result

▲▲
●●
▲▲
3 → 3

Each row keeps its place and gains its one matching referenced row. The newly joined table is the referenced side.

->  descend — rows can multiply

rows so far

▲
●
→FOR KEY ->

result

▲▲
▲▲
●●
●●
●●
2 → 5

Each row expands to its children — the only direction where the running count grows. The newly joined table is the referencing side.

Chain only <- joins, all INNER or LEFT: the result stays 1 : 1 with the first table in the FROM clause.

Four forms are guaranteed 1 : 1 with the referencing side — the ones that never null-extend the referenced side:

  • … [INNER] JOIN f FOR KEY (…) -> p (…)
  • … RIGHT [OUTER] JOIN f FOR KEY (…) -> p (…)
  • … [INNER] JOIN p FOR KEY (…) <- f (…)
  • … LEFT [OUTER] JOIN p FOR KEY (…) <- f (…)

The rule: the outer-preserved side, if any, must be the referencing side.

The proof

The DBMS checks three things

A key join is correct exactly when these three hold. The DBMS proves all three at compile time, or rejects the query — it never accepts a key join it cannot prove.

Check 1

Referenced side is unique

The referenced columns must identify at most one matching row at the point of the join. A previous join can break this even if the base table has a primary key.

Check 2

FK values are covered

Every all-non-null referencing value must be provably present on the referenced side. Filtered views must keep matching filters aligned.

Check 3

Nullable FKs are handled

An inner key join needs the referencing columns known not null. If they can be null, use an outer key join that preserves the referencing rows.

Rejected at compile time

Four ways a join fails the proof

These aren't heuristics for known-bad patterns. Each query below fails the proof, so it never compiles — and anything FOR KEY cannot prove correct is rejected the same way. Every example is complete: create the tables, run the query, and you get the exact error shown.

No matching FK

The join columns don't match any foreign key

Composite keys are a classic trap: forget one column and the join looks right on small test data, then quietly returns duplicates in production.

Composite FK, one column forgotten 
CREATE TABLE hotels (
    id          integer PRIMARY KEY
);
CREATE TABLE rooms (
    hotel_id    integer NOT NULL REFERENCES hotels (id),
    room_number integer NOT NULL,
    PRIMARY KEY (hotel_id, room_number)
);
CREATE TABLE reservations (
    id          integer PRIMARY KEY,
    hotel_id    integer NOT NULL,
    room_number integer NOT NULL,
    FOREIGN KEY (hotel_id, room_number) REFERENCES rooms (hotel_id, room_number)
);

SELECT *
FROM reservations AS res
JOIN rooms AS r FOR KEY (room_number) <- res (room_number);

ERROR:  key join from referencing relation res to referenced relation r cannot be proven
LINE 3: JOIN rooms AS r FOR KEY (room_number) <- res (room_number);
                        ^
DETAIL:  There is no matching foreign key constraint for res (room_number) referencing r (room_number).

Fix: name both key columns — FOR KEY (hotel_id, room_number) <- res (hotel_id, room_number).

Fails Check 1

The referenced side isn't unique here (fan-trap)

Joining one parent to two child tables multiplies its rows, so the second key join can no longer prove the parent unique — exactly what would have doubled the sums.

Two children fanned out from orders 
CREATE TABLE orders (
    id integer PRIMARY KEY
);
CREATE TABLE order_items (
    id       integer PRIMARY KEY,
    order_id integer NOT NULL REFERENCES orders (id),
    amount   numeric
);
CREATE TABLE payments (
    id       integer PRIMARY KEY,
    order_id integer NOT NULL REFERENCES orders (id),
    amount   numeric
);

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 4: 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.

Fix: pre-aggregate order_items and payments to one row per order_id, then key-join those subqueries to orders.

Fails Check 2

Some referencing rows would have no match

The view hides inactive customers, so an order pointing at one has nowhere to land. An ordinary join would silently drop it; the key join refuses.

Referenced side filtered by a view 
CREATE TABLE customers (
    id     integer PRIMARY KEY,
    active boolean NOT NULL DEFAULT true
);
CREATE TABLE orders (
    id          integer PRIMARY KEY,
    customer_id integer NOT NULL REFERENCES customers (id)
);
CREATE VIEW active_customers AS
    SELECT * FROM customers WHERE active;

SELECT *
FROM orders AS o
JOIN active_customers AS ac FOR KEY (id) <- o (customer_id);

ERROR:  key join from referencing relation o to referenced relation ac cannot be proven
LINE 3: JOIN active_customers AS ac FOR KEY (id) <- o (customer_id);
                                    ^
DETAIL:  Not every o (customer_id) value can be proven to have a matching ac row. Referenced relation ac is filtered before this key join. The relevant operation occurs inside view public.active_customers.

Fix: key-join the base customers table and filter in WHERE, or give both sides the same key filter.

Fails Check 3

An inner join could silently drop rows

home_dept is optional, so a NULL matches nothing. An inner join would quietly drop employees who have no home department.

Inner join on a nullable FK 
CREATE TABLE departments (
    id integer PRIMARY KEY
);
CREATE TABLE employees (
    id        integer PRIMARY KEY,
    home_dept integer REFERENCES departments (id)   -- nullable
);

SELECT *
FROM employees AS e
JOIN departments AS d FOR KEY (id) <- e (home_dept);

ERROR:  key join from referencing relation e to referenced relation d cannot be proven
LINE 3: JOIN departments AS d FOR KEY (id) <- e (home_dept);
                              ^
DETAIL:  This inner join could filter rows from e. Referencing columns e (home_dept) can be null.

Fix: use LEFT JOIN departments AS d FOR KEY (id) <- e (home_dept) to keep employees with no home department (null-extended).

Views and CTEs

Derived tables are allowed when the proof survives

A query author should not need to care whether they are joining a base table, a view, or a CTE. The schema designer owns the derived table; the DBMS checks whether it still exposes the facts a key join needs.

  • Columns must be traceable. Projecting id is fine; hiding it inside COALESCE, casts, or arbitrary expressions breaks the proof.
  • The PK side needs row coverage. It must not filter away referenced key values that the FK side can still ask for.
  • The PK side needs uniqueness. Joins can duplicate rows; grouping by the key can sometimes restore uniqueness.
  • Not-null evidence is tracked through joins. A prior outer join can null-extend a column that was not null in the base table.
View that works 
CREATE VIEW department_directory AS
  SELECT id, name
  FROM departments;

SELECT e.name, dd.name
FROM employees AS e
JOIN department_directory AS dd
  FOR KEY (id) <- e (dept_id);

Design calls

Why this shape?

The syntax is intentionally explicit. It is optimized for local readability, schema evolution, and stored definitions that fail loudly when their assumptions break.

Why arrows?

They make FK direction visible at the join site. In a chain of <- joins, the first table's rows are preserved end-to-end.

Why explicit columns?

Constraint-name and inference-based designs get ambiguous with aliases, views, multiple FKs, and schema changes. Column lists state the contract directly.

Why compile time?

Runtime checks depend on today's data. Key joins prove the structure before the query runs, so bad joins do not wait for production data to reveal themselves.

Why no indirect FK magic?

A key join follows one referential constraint. If the path is A -> B -> C, write both joins so each step is readable and independently checked.