Finding the odd row between two tables in Oracle

oracle

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.

Best Answer

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

Related Solutions

Optimizing a compare query

Your requirement and logic behind the requirement makes sense, in theory. However, how quickly this can be achieved depends upon the data volume in tables A and B as well as availability of any useful indexes. Worst case scenario is if both the tables A and B have large volume of data and there are no useful indexes available on the tables. In that case (and provided table statistics are close to actual data), oracle will not be able to "find first unmatched record" any quicker than getting a count (provided you change your query to the LEFT JOIN approach mentioned above). Best case scenario is either both the tables A and B or table A has small volume of data and/or there are indexes on both tables on the combination of X, Y and Z columns. In this case, following query may perform better than doing a COUNT

SELECT COUNT(*) FROM DUAL
WHERE NOT EXISTS
(
    SELECT NULL
    FROM A LEFT JOIN B
    ON  A.X=B.X AND A.Y=B.Y AND A.Z=B.Z
    WHERE B.X IS NULL
) ;

Oracle – Select the Max from Two Max Values

Here's one possibility:

with departures(departure_id, cnt) as (
    select f.departure_id, count(t.id_passenger)
    from flight f
    join takes t
        on f.id = t.id_flight
    where year(departure_date) = ?
    group by f.departure_id
),
arrivals as (arrival_id, cnt) as (
    select f.arrival_id, count(t.id_passenger)
    from flight f
    join takes t
        on f.id = t.id_flight
    where year(arrival_date) = ?
    group by f.arrival_id
)
select position, max(cnt)
from (
    select arrival_id as position, cnt
    from arrivals
    union
    select departure_id as position, cnt
    from departures
) as t;

Best Answer

Related Solutions

Optimizing a compare query

Oracle – Select the Max from Two Max Values

Related Question