30-second SQL standard proposal

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

JOIN ... FOR KEY is a foreign-key-aware equijoin with a compile-time proof. If the DBMS can prove the join follows a real referential constraint and will not silently lose or duplicate the rows being preserved, it prepares the query. Otherwise, it fails before execution.

See the syntax See the three checks

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.

3Bad joins fail early

The DBMS rejects joins it cannot prove safe from declarations and query structure.

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 is supposed to follow.

Plain equijoin 
SELECT e.name, d.name AS department
FROM employees AS e
JOIN departments AS d
  ON d.dept_id = e.dept_id
WHERE e.emp_id = 4017;
Key join 
SELECT e.name, d.name AS department
FROM employees AS e
JOIN departments AS d
  FOR KEY (dept_id) <- e (dept_id)
WHERE e.emp_id = 4017;

Read it as: join departments to employees by following the referential constraint from employees.dept_id to departments.dept_id.

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

The proof

The DBMS checks three things

A key join preserves the referencing rows and enriches each all-non-null referencing key with one referenced row. These checks are the compile-time version of that promise.

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.

Bugs it catches

Silent wrong results become compile-time errors

The point is not shorter syntax. The point is to make structural join assumptions executable by the DBMS.

Rejected

Wrong composite join

Joining hotel rooms by room_number alone can pass tests when the data is tiny. A key join rejects it because the named columns do not match the declared referential constraint.

Missing hotel_id 
JOIN rooms AS r
  FOR KEY (room_number) <- res (room_number)

Rejected

Nullable FK with inner join

A nullable FK does not violate the constraint, but NULL does not match anything. An inner key join would drop those rows, so the DBMS rejects it.

Use LEFT when FK can be null 
JOIN customers AS c
  FOR KEY (id) <- o (customer_id)

-- Repair:
LEFT JOIN customers AS c
  FOR KEY (id) <- o (customer_id)

Rejected

Fan-trap aggregate

Joining one referenced table to two child tables can multiply rows and inflate sums. Key joins notice when the referenced side is no longer proven unique.

Aggregate hazard 
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)

-- Repair: pre-aggregate each child table first.

Rejected

Filtered view mismatch

If the PK-side view filters to the current tenant but the FK-side view does not, some referencing rows may lose their match. Matching key filters repair the proof.

Tenant filter mismatch 
CREATE VIEW tenant_customers AS
  SELECT * FROM customers
  WHERE tenant_id = CURRENT_USER;

-- tenant_orders needs the matching tenant filter too.

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 dept_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 dept_id, name
  FROM departments;

SELECT e.name, dd.name
FROM employees AS e
JOIN department_directory AS dd
  FOR KEY (dept_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.