Oracle combines two records in one record despite of Union

Tags: , ,

I have created two simple queries to combine the records using UNION

However, I faced a problem in one case, the data in the 1st table is exactly like the 2nd one

Then the result appears in one record, not two records as others I need them in separate lines

For example, I have two tables for bank

BankAccount1 & BankAccount2

select ID, REF, Amount 
    (select ID1, REF1, Amount1 from BankAccount1 
     ID2, REF2, Amount2 from BankAccount2)

The result should be like:

ID    REF         Amount
1     10101040      200
1     10101040      200

but Oracle shows result like this

ID    REF        Amount
1     10101040     200

What do you recommend to solve this issue?


From the The UNION [ALL], INTERSECT, MINUS Operators documentation:

The UNION operator returns only distinct rows that appear in either result, while the UNION ALL operator returns all rows. The UNION ALL operator does not eliminate duplicate selected rows

So you need to use UNION ALL instead of UNION:

SELECT ID1 AS id, REF1 AS ref, Amount1 AS amount
FROM   BankAccount1 
SELECT ID2, REF2, Amount2
FROM   BankAccount2

So, for your sample data:

CREATE TABLE bankaccount1 ( id1, ref1, amount1 ) AS
SELECT 1, 10101040, 200 FROM DUAL;

CREATE TABLE bankaccount2 ( id2, ref2, amount2 ) AS
SELECT 1, 10101040, 200 FROM DUAL;

This outputs:

ID |      REF | AMOUNT
-: | -------: | -----:
 1 | 10101040 |    200
 1 | 10101040 |    200

db<>fiddle here

(Note: Your query is syntactically invalid as you need a SELECT after the UNION and the column names will be the taken from the first SELECT of the UNION and the outer SELECT would expect the columns ID1, REF1, Amount1 rather than ID, REF, Amount.)

Source: stackoverflow