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:
| Traditional JOIN | JOIN TO ONE | |
|---|---|---|
| Connection trap | ⚠️ Silently produces wrong results | ⛔️ Blocked at parse/runtime |
| Row multiplication | ⚠️ Cartesian per shared parent | ⛔️ Prevented by RWOQ enforcement |
| Aggregates | ⚠️ Inflated or deflated silently | ✅ Guaranteed by one-to-one mapping |
Equivalent of TOO_MANY_ROWS
|
❌ Not available in joins | 🛑 Runtime error on violated uniqueness |
| Developer awareness | ⚠️ Can go unnoticed for years | 🛑 Immediate error |
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.
Top comments (0)