Oracle AI Database 23.26.2 introduced the JOIN TO ONE syntax to address a common problem with SQL joins: a query can unexpectedly return multiple rows for a row in the main table.
This is especially useful when a query is designed to widen a table without increasing its number of rows. With JOIN TO ONE, Oracle raises an error when a subject row matches multiple rows in a joined table instead of silently returning the multiplied rows.
In this article, we will use three tables:
CUSTOMERS
1
|
M
ACCOUNTS
1
|
M
TRANSACTIONS
The relationships are:
- One customer can have multiple accounts.
- Each account belongs to one customer.
- One account can have multiple transactions.
- Each transaction belongs to one account. For example:
Vahid Yousefzadeh
|
+-- A1001
|
+-- Transaction 1
+-- Transaction 2
+-- Transaction 3
The Normal JOIN
Suppose we want to display each account together with its customer:
SELECT a.account_number, c.customer_name
FROM accounts a
JOIN customers c
ON c.customer_id = a.customer_id;
ACCOUNT_NUMBER CUSTOMER_NAME
-------------------- ------------------------------
A1001 Vahid Yousefzadeh
A1002 Ebne moljem moradi
A1003 Payan Rafat
There is one row for each account. This works because every account belongs to exactly one customer.
Now suppose we want to show the same customer information only for accounts that have transactions:
SELECT a.account_number, c.customer_name
FROM accounts a
JOIN customers c
ON c.customer_id = a.customer_id
JOIN transactions t
ON t.account_id = a.account_id;
ACCOUNT_NUMBER CUSTOMER_NAME
-------------------- ------------------------------
A1001 Vahid Yousefzadeh
A1001 Vahid Yousefzadeh
A1001 Vahid Yousefzadeh
A1002 Ebne moljem moradi
A1002 Ebne moljem moradi
A1003 Payan Rafat
6 rows selected.
As you can see, some rows are duplicated. The main reason is that A1001 has three transactions:
A1001
|
+-- Transaction 1
+-- Transaction 2
+-- Transaction 3
Therefore, the normal join returns the account once for each matching transaction.
This is normal behavior for a one-to-many join. The problem is that our query may have been intended to return one row per account.
Traditional joins do not automatically identify this as a problem.
Using JOIN TO ONE
Oracle AI Database 26ai provides JOIN TO ONE for exactly this type of requirement.
We can write:
SQL> SELECT a.account_number, c.customer_name
FROM accounts a
JOIN TO ONE(customers c, transactions t
ON t.account_id = a.account_id);
ERROR at line 3:
ORA-18640: JOIN TO ONE reached multiple rows joining to "T", resulting in a non-unique join
Help: https://docs.oracle.com/error-help/db/ora-18640/
We are saying that the rows from ACCOUNTS should remain one row per account after the join.
However, A1001 matches three rows in TRANSACTIONS.
Therefore Oracle raises an error instead of silently returning:
A1001 Vahid Yousefzadeh
A1001 Vahid Yousefzadeh
A1001 Vahid Yousefzadeh
This tells us that the JOIN TO ONE requirement has been violated.
The important point is that JOIN TO ONE does not mean an account cannot have multiple transactions. The ACCOUNTS → TRANSACTIONS relationship is still one-to-many. It means that when ACCOUNTS is the subject table, each account is expected to remain a single row in the result.
What If TRANSACTIONS Is Only a Filter?
Now consider a different requirement:
Show accounts that have at least one transaction. We don’t actually need any columns from TRANSACTIONS. Using a normal join would unnecessarily multiply the account rows.
Instead, we can use EXISTS:
SQL> SELECT a.account_number, c.customer_name
FROM accounts a
JOIN customers c
ON c.customer_id = a.customer_id
WHERE EXISTS
(SELECT 1 FROM transactions t WHERE t.account_id = a.account_id);
ACCOUNT_NUMBER CUSTOMER_NAME
-------------------- ------------------------------
A1001 Vahid Yousefzadeh
A1002 Ebne moljem moradi
A1003 Payan Rafat
It doesn’t matter that A1001 has three transactions. We only want to know whether a transaction exists.
When a many-side table is being used only to filter the subject table, a semijoin such as EXISTS is an appropriate approach.
What If We Actually Need Transaction Data?
Suppose our requirement changes. Now we want: Display every transaction together with its account.
In this case, TRANSACTIONS should become the subject table.
SQL> SELECT t.transaction_id,
t.amount,
a.account_number
FROM transactions t
JOIN TO ONE (
accounts a
);
TRANSACTION_ID AMOUNT ACCOUNT_NUMBER
-------------- ---------- --------------------
1 500 A1001
2 250 A1001
3 100 A1001
4 800 A1002
5 150 A1002
6 1150 A1003
6 rows selected.
Each transaction belongs to one account.
Therefore, each transaction can be widened with account information without multiplying the transaction row.
The order of the tables in JOIN TO ONE is important because Oracle processes the joins from the subject table from left to right.
How Does Oracle Find the Join Condition?
In our example, we created these foreign keys:
CONSTRAINT accounts_customer_fk
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
and:
CONSTRAINT transactions_account_fk
FOREIGN KEY (account_id)
REFERENCES accounts(account_id)
Oracle can use foreign-key relationships to infer the join conditions for JOIN TO ONE.
We can also specify the join condition explicitly:
SQL> SELECT a.account_number, c.customer_name
FROM accounts a
JOIN TO ONE(customers c
ON c.customer_id = a.customer_id);
ACCOUNT_NUMBER CUSTOMER_NAME
-------------------- ------------------------------
A1001 Vahid Yousefzadeh
A1002 Ebne moljem moradi
A1003 Payan Rafat
Using an explicit ON condition can make the intended relationship clearer, particularly when there are multiple foreign keys between tables or the schema changes later.
Top comments (0)