0

I have two tables with the following schema

id, fruit

In TableA the values are like this:

A01, Apple
A02, Orange
A03, Pineapple
A03, Pineapple
A05, Banana

In TableB the values are like this:

A01, Apple
A02, Orange
A03, Pineapple
A04, Pineapple
A05, Banana

How can I reconcile these two tables to return just the duplicate row A03 in TableA? Like a one-to-one comparison of the tuples and returning the odd tuple.

I tried the minus query like below

Select * from TableA
minus
Select * from TableB

But that's not returning the result set I expected.

1 Answer 1

4

One-to-one based on what?

You have no reliable and permanent way of identifying/sorting rows in TableA. You have A03, Pineapple twice. Which will be the 3rd and which will be the 4th?

Sure, you can do the below, but forget this right away and fix your data. Rowids can change when moving data so the order is never guaranteed.

create table tablea(id varchar2(3 char), fruit varchar2(9 char));
create table tableb(id varchar2(3 char), fruit varchar2(9 char));

insert into tablea values ('A01', 'Apple');
insert into tablea values ('A02', 'Orange');
insert into tablea values ('A03', 'Pineapple');
insert into tablea values ('A03', 'Pineapple');
insert into tablea values ('A05', 'Banana');

insert into tableb values ('A01', 'Apple');
insert into tableb values ('A02', 'Orange');
insert into tableb values ('A03', 'Pineapple');
insert into tableb values ('A04', 'Pineapple');
insert into tableb values ('A05', 'Banana');

commit;

select
  a.id ida, a.fruit fruita,
  b.id idb, b.fruit fruitb
from
  (select t.*, row_number() over (order by id, rowid) as rn from tablea t) a
join
  (select t.*, row_number() over (order by id, rowid) as rn from tableb t) b
on (a.rn = b.rn and (a.id != b.id or a.fruit != b.fruit));

IDA FRUITA    IDB FRUITB   
--- --------- --- ---------
A03 Pineapple A04 Pineapple

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.