Codd's Connection Trap and Oracle's JOIN TO ONE

In a previous post, I explored Codd's connection trap in PostgreSQL and MongoDB — the classic pitfall where joining two independent many-to-many relationships through a shared attribute produces spurious combinations that look like facts but aren't.

The example followed Codd's 1970 suppliers–parts–projects model: we know which suppliers supply which parts, and which projects use which parts, but joining through parts to derive supplier–project relationships is a relational composition — it tells us what could be true, not what is true.

Oracle Database 26ai introduces JOIN TO ONE, a SQL extension that structurally prevents this class of errors. In this post, I'll reproduce Codd's connection trap in Oracle, show how JOIN TO ONE catches it, and demonstrate the correct solutions.

Why This Matters: A Gap in SQL joins

When developers build joins at the application level — fetching a parent row for a given foreign key in PL/SQL or application code — they naturally get safety checks: TOO_MANY_ROWS tells them a lookup that should have been unique returned multiple rows, and NO_DATA_FOUND tells them the expected parent doesn't exist. These exceptions act as guardrails, catching data or logic errors immediately.

But when the same logic moves into a SQL JOIN, those guardrails disappear. A join that silently matches multiple rows simply multiplies the result set — no error, no warning, just quietly wrong numbers. A join that finds no match either drops the row (inner join) or pads it with NULLs (outer join), but never raises an alarm about violated assumptions.

JOIN TO ONE bridges this gap. It brings the equivalent of TOO_MANY_ROWS protection into SQL joins: if a join that you declared as "to one" ever reaches a second row, Oracle raises a runtime error instead of silently corrupting your results. The default outer-join behavior handles the "zero matches" case gracefully (like a NO_DATA_FOUND that returns NULL columns instead of erroring), and you can override it to INNER JOIN TO ONE when the absence of a match should eliminate the row.

A note on naming: JOIN TO ONE is semantically JOIN TO ZERO OR ONE AND ONLY ONE (for the default outer case) or JOIN TO ONE AND ONLY ONE (for the inner case). SQL has never been shy about verbosity, so a more precise name might have been warranted.

Schema & Sample Data

Following Codd's example, and the previous blog post, we have suppliers, parts, projects, and two independent many-to-many relationships — now with quantities to make the consequences of the trap concrete:

CREATE TABLE suppliers (
    supplier_id VARCHAR2(10) PRIMARY KEY
);

CREATE TABLE parts (
    part_id VARCHAR2(10) PRIMARY KEY
);

CREATE TABLE projects (
    project_id VARCHAR2(10) PRIMARY KEY
);

-- Supplier supplies parts
CREATE TABLE supplier_part (
    supplier_id   VARCHAR2(10) REFERENCES suppliers,
    part_id       VARCHAR2(10) REFERENCES parts,
    qty_available INT NOT NULL,
    PRIMARY KEY (supplier_id, part_id)
);

-- Project uses parts
CREATE TABLE project_part (
    project_id VARCHAR2(10) REFERENCES projects,
    part_id    VARCHAR2(10) REFERENCES parts,
    qty_needed INT NOT NULL,
    PRIMARY KEY (project_id, part_id)
);

-- Reference data
INSERT INTO suppliers VALUES ('S1');
INSERT INTO suppliers VALUES ('S2');

INSERT INTO parts VALUES ('P1');
INSERT INTO parts VALUES ('P2');
INSERT INTO parts VALUES ('P3');

INSERT INTO projects VALUES ('Alpha');
INSERT INTO projects VALUES ('Beta'); 

-- S1 supplies P1 (100 units) and P2 (200 units)
-- S2 supplies P2 (150 units) and P3 (300 units)
INSERT INTO supplier_part VALUES ('S1', 'P1', 100);
INSERT INTO supplier_part VALUES ('S1', 'P2', 200);
INSERT INTO supplier_part VALUES ('S2', 'P2', 150);
INSERT INTO supplier_part VALUES ('S2', 'P3', 300);

-- Alpha uses P1 (50 units) and P2 (75 units)
INSERT INTO project_part VALUES ('Alpha', 'P1', 50);
INSERT INTO project_part VALUES ('Alpha', 'P2', 75);

-- Alpha uses P1 (50 units) and P2 (75 units)  
-- Beta  uses P2 (60 units) and P3 (90 units)  
INSERT INTO project_part VALUES ('Alpha', 'P1', 50);  
INSERT INTO project_part VALUES ('Alpha', 'P2', 75);  
INSERT INTO project_part VALUES ('Beta',  'P2', 60);  
INSERT INTO project_part VALUES ('Beta',  'P3', 90); 

COMMIT;

The Connection Trap in Action

A developer wants to know which suppliers are connected to which projects and executes the following query:

SELECT sp.supplier_id,
       pp.project_id,
       sp.part_id,
       sp.qty_available,
       pp.qty_needed
FROM   supplier_part sp
JOIN   project_part pp ON sp.part_id = pp.part_id
ORDER  BY sp.supplier_id, sp.part_id
;

SUPPLIER_ID    PROJECT_ID    PART_ID       QTY_AVAILABLE    QTY_NEEDED
______________ _____________ __________ ________________ _____________
S1             Alpha         P1                      100            50
S1             Alpha         P2                      200            75
S1             Beta          P2                      200            60
S2             Alpha         P2                      150            75
S2             Beta          P2                      150            60
S2             Beta          P3                      300            90

6 rows selected.

6 rows from only 4 supplier-part rows and 4 project-part rows. The query asserts, for example, "S2 supplies P2 to Alpha" — but our data only says S2 can supply P2 and Alpha needs P2. The join inferred relationships through the shared attribute part_id that were never recorded as facts.

As Codd warned in his 1970 paper, this is exactly the connection trap: deriving relationships that were never asserted.

The Damage with Aggregates

Now the developer summarizes:

SELECT sp.supplier_id,
       SUM(sp.qty_available) AS total_available,
       SUM(pp.qty_needed)    AS total_needed
FROM   supplier_part sp
JOIN   project_part pp ON sp.part_id = pp.part_id
GROUP  BY sp.supplier_id
ORDER  BY sp.supplier_id
;

SUPPLIER_ID       TOTAL_AVAILABLE    TOTAL_NEEDED
______________ __________________ _______________
S1                            500             185
S2                            600             225

Compare with the actual totals from each table independently:

SELECT supplier_id, SUM(qty_available) AS total_available  
FROM   supplier_part  
GROUP  BY supplier_id  
ORDER  BY supplier_id;  

SUPPLIER_ID       TOTAL_AVAILABLE
______________ __________________
S1                            300
S2                            450

SELECT project_id, SUM(qty_needed) AS total_needed  
FROM   project_part  
GROUP  BY project_id  
ORDER  BY project_id;  

PROJECT_ID       TOTAL_NEEDED
_____________ _______________
Alpha                     125
Beta                      150

The connection trap inflated both sides:

• S1's availability jumped from 300 to 500: P2's 200 was counted twice (once for Alpha, once for Beta)
• S2's availability jumped from 450 to 600: P2's 150 was counted twice (once for Alpha, once for Beta)
• Needs were scrambled: the 225 attributed to S2 mixes Alpha's and Beta's needs, double-counting P2's demand

The trap corrupts aggregates in whichever direction the data happens to push — inflation, deflation, or both at once — and it does so silently. In application code, a lookup-by-key that returns two rows would raise TOO_MANY_ROWS. In a SQL join, the same situation just silently multiplies your totals.

This explains why, in a data warehouse, we denormalize into a dimensional model, or star schema, with a single fact table and dimension tables. Normalization makes the relational schema unsafe for users who see only the SQL schema, without the details of the domain model or the safeguards provided by the application.

The Join Graph: Why There Is No RWT

Oracle's JOIN TO ONE documentation introduces the concept of a Row-Widened Table (RWT) — a table from which all other tables can be reached through unique (many-to-one) joins, ensuring the query result maps one-to-one to the RWT rows. A query where such an RWT exists is a Row Widening Only Query (RWOQ), and it's almost always what you need for correct results.

Here's the join graph of our broken query:

   supplier_part ──→ parts ←── project_part
         (FK)        (PK)         (FK)

The "parts" table is a parent node reached from two sibling child nodes. This is the chasm trap:

• Starting from "supplier_part": the path to "project_part" via "parts" goes many-to-one then one-to-many — not unique
• Starting from "project_part': same problem in reverse
• Starting from "parts": both children fan out

No table qualifies as a RWT. This is not a RWOQ. The output rows don't map one-to-one to any table's rows.

How JOIN TO ONE Catches the Trap

With Oracle 26ai's JOIN TO ONE, attempting to write this query produces an error:

-- THIS FAILS — and that's exactly what we want  
SELECT sp.supplier_id,  
       pp.project_id,  
       sp.qty_available,  
       pp.qty_needed  
FROM   supplier_part sp  
JOIN TO ONE (parts p, project_part pp); 

Error at Command Line : 6 Column : 23
Error report -
SQL Error: ORA-18641: No join key found for "PROJECT_PART"

JOIN TO ONE requires every table inside the parentheses to be reachable from the leading RWT through a chain of unique joins. The path supplier_part → parts is many-to-one (valid), but parts → project_part is one-to-many (invalid). Oracle detects that part_id alone is not unique in project_part (the PK is (project_id, part_id)) and blocks the query.

Even forcing an explicit ON clause doesn't help:

SELECT sp.supplier_id,  
       pp.project_id,  
       sp.qty_available,  
       pp.qty_needed 
FROM supplier_part sp
JOIN TO ONE (
    parts p,
    project_part pp ON p.part_id = pp.part_id
);

Error at Command Line : 8 Column : 5
Error report -
SQL Error: ORA-18640: JOIN TO ONE reached multiple rows joining to "PP", resulting in a non-unique join

https://docs.oracle.com/error-help/db/ora-18640/

Oracle either rejects at parse time or raises a runtime error the moment a part matches multiple project_part rows — the SQL equivalent of the TOO_MANY_ROWS exception that application developers rely on. Instead of silently producing wrong numbers for months or years, you get an immediate, clear signal: this query structure doesn't support the one-to-one mapping you're claiming.

The Correct Solutions: two Separate RWOQs

Since the schema doesn't record the three-way relationship, we must run two separate queries, and that's exactly what JOIN TO ONE forces us to do:

-- RWOQ 1: "What can each supplier supply?"  
-- RWT = supplier_part → unique joins to suppliers and parts  
SELECT sp.supplier_id,  
       sp.part_id,  
       sp.qty_available  
FROM   supplier_part sp  
JOIN TO ONE (suppliers s, parts p)  
ORDER  BY sp.supplier_id, sp.part_id;  

SUPPLIER_ID    PART_ID       QTY_AVAILABLE
______________ __________ ________________
S1             P1                      100
S1             P2                      200
S2             P2                      150
S2             P3                      300

-- RWOQ 2: "What does each project need?"  
-- RWT = project_part → unique joins to projects and parts  
SELECT pp.project_id,  
       pp.part_id,  
       pp.qty_needed  
FROM   project_part pp  
JOIN TO ONE (projects j, parts p)  
ORDER  BY pp.project_id, pp.part_id;  

PROJECT_ID    PART_ID       QTY_NEEDED
_____________ __________ _____________
Alpha         P1                    50
Alpha         P2                    75
Beta          P2                    60
Beta          P3                    90

Both are valid RWOQs. Clean star-shaped join graphs. No spurious combinations. Aggregates on qty_available or qty_needed are guaranteed correct.

Conclusion

Codd identified the connection trap in 1970: inferring relationships from shared attributes produces combinations that could be true, not combinations that are true. Over fifty years later, this trap remains one of the most common sources of silently wrong SQL — aggregates that are "slightly off," duplicates masked by DISTINCT, totals that nobody questions because they look plausible.

Application developers have long relied on TOO_MANY_ROWS and NO_DATA_FOUND exceptions to catch violated uniqueness assumptions in procedural lookups. But the moment those lookups become SQL joins, the safety net vanishes — a many-to-one assumption that silently becomes many-to-many just multiplies rows without complaint.

Oracle's JOIN TO ONE in Database 26ai brings that safety net back into SQL:

The rule is the same whether you use normalized relations, star schemas, or document models: if a relationship is a fact, it must be stored as one — not derived through joins. JOIN TO ONE ensures that when you do join, the result stays faithful to the facts your schema actually records, or the query fails.

If you think SQL databases, normalization, and referential integrity automatically protect data consistency better than denormalized models, this is proof that they do not. A document model can preserve business invariants by storing them consistently, whereas normalization can break them across multiple tables to be joined. The relational model is an abstraction that simplifies data relationships and can hide business invariants that exist in the domain model and the application. Applications must then compensate by writing safer queries, often at a performance cost by running multiple queries. The new JOIN TO ONE syntax helps SQL users find the right balance by declaring their intent: to look up additional columns from dimensions without changing the number of fact rows.

If you're used to MongoDB, this RWOQ (Row Widening Only Query) is simply a $lookup. It doesn't change the cardinality, but adds an array that can be:

• an empty array (similar to NO_DATA_FOUND and LEFT OUTER JOIN)
• a single-item array (as expected with JOIN TO ONE)
• a multi-item array (equivalent to TOO_MANY_ROWS if unexpected) There's no risk of silent row multiplication, because when that expansion is expected, it must be made explicit with $unwind.